﻿using ciji.Application.Dto;
using ciji.Core;
using Furion.DatabaseAccessor;
using Microsoft.AspNetCore.Http;
using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Logging;
using Newtonsoft.Json;
using Pipelines.Sockets.Unofficial.Arenas;
using System;
using System.Drawing;
using System.Drawing.Printing;
using System.Reflection;
using System.Security.Cryptography;
using System.Security.Policy;
using System.Text.RegularExpressions;
using static System.Runtime.InteropServices.JavaScript.JSType;

namespace ciji.Application.Data
{
 
    /// <summary>
    /// 项目管理
    /// </summary>
    [ApiDescriptionSettings("项目管理", Order = 500, Description = "项目管理")]
    [LoggingMonitor]
    public class DataService : IDynamicApiController
    {

        private SqlSugarRepository<cts_Area_Year> _areaYearRep;
        private SqlSugarRepository<cts_PersonGroup> _personGroupRep;
        private SqlSugarRepository<cts_GroupTown> _groupTownRep;
        private SqlSugarRepository<cts_AssessPerson> _assessPersonRep;
        private SqlSugarRepository<cts_AssessInfo> _assessInfoRep;
        private SqlSugarRepository<cts_AssessImg> _assessImgRep;
        private SqlSugarRepository<cts_CompanySign> _companySignRep;
        private SqlSugarRepository<cts_Employee> _employeeRep;
        private SqlSugarRepository<cts_AssessUpdate> _assessUpdateRep;
        
        public DataService(SqlSugarRepository<cts_Area_Year> areaYearRep
            , SqlSugarRepository<cts_PersonGroup> personGroupRep
            , SqlSugarRepository<cts_GroupTown> groupTownRep
            , SqlSugarRepository<cts_AssessPerson> assessPersonRep
            , SqlSugarRepository<cts_AssessInfo> assessInfoRep
            , SqlSugarRepository<cts_AssessImg> assessImgRep
            , SqlSugarRepository<cts_CompanySign> companySignRep
            , SqlSugarRepository<cts_Employee> employeeRep
            , SqlSugarRepository<cts_AssessUpdate> assessUpdateRep)
        {
            _areaYearRep = areaYearRep;
            _personGroupRep = personGroupRep;
            _groupTownRep = groupTownRep;
            _assessPersonRep = assessPersonRep;
            _assessInfoRep = assessInfoRep;
            _assessImgRep= assessImgRep;
            _companySignRep = companySignRep;
            _employeeRep = employeeRep;
            _assessUpdateRep= assessUpdateRep;
        }
        #region 项目管理 
        /// <summary>
        /// 获取年份地区分组
        /// </summary>
        /// <param name="type"></param>
        /// <returns></returns>
        public List<YearMenuDto> GetAreaYear(int type = 0)
        {
            var list = _areaYearRep.AsQueryable().Where(m => m.Type == type)
           .Where(m => !string.IsNullOrEmpty(m.YearName))
           .Where(m => !string.IsNullOrEmpty(m.AreaName))
           .Select(m => new { m.YearName, m.AreaName })
           .Distinct()
           .OrderBy(m => m.YearName, OrderByType.Desc)
           .ToList();

            var tmpYear = "";
            var finalList = new List<YearMenuDto>();
            var tmpMenu = new YearMenuDto();

            foreach (var item in list)
            {
                var tmpChild = new YearMenuChild();
                tmpChild.YearName = item.YearName;
                tmpChild.AreaName = item.AreaName;
                if (!item.YearName.Equals(tmpYear))
                {
                    tmpYear = item.YearName;
                    tmpMenu = new YearMenuDto();
                    tmpMenu.Area = new List<YearMenuChild>();
                    tmpMenu.Year = tmpYear;
                    finalList.Add(tmpMenu);
                }
                tmpMenu.Area.Add(tmpChild);
            }
            return finalList;

        }

        /// <summary>
        /// 查询人员分组
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public List<cts_PersonGroup> SearchPersonGroup(SearchPersonGroupDto input)
        {
            return _personGroupRep.AsQueryable()
           .Where(m => m.IsSaved == input.issaved)
           .Where(m => m.Type == input.type)
           .WhereIF(!string.IsNullOrEmpty(input.str),m => m.GroupName.Contains(input.str))
           .OrderByDescending(m => m.Id).ToList();
        }
        /// <summary>
        /// 查询人员分组-带分页
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        [AllowAnonymous]
        public SqlSugarPagedXList<cts_PersonGroup> SearchPersonGrouphasPage(SearchPersonGrouphasPageDto input)
        {

            var query = _personGroupRep.AsQueryable()
                .Where(m => m.sys_IsDelete == 0 && m.IsSaved == input.issaved && m.Type == input.type)
                .WhereIF(!string.IsNullOrEmpty(input.str), m => m.GroupName.Contains(input.str))
                .WhereIF(!string.IsNullOrEmpty(input.year), m => m.GroupYear == input.year)
                .WhereIF(!string.IsNullOrEmpty(input.area), m => m.GroupArea == input.area);
               


            var subQuery = _assessPersonRep.AsQueryable()
                .LeftJoin<cts_AssessInfo>((a, b) => a.Gid == b.AssessPeresonId && a.sys_IsDelete == 0 && b.sys_IsDelete == 0)
                .GroupBy(a => a.GroupId)
                .Select((a, b) => new
                {
                    GroupId = a.GroupId,
                    PersonCount = SqlFunc.AggregateCount(1),
                    PersonCount2 = SqlFunc.AggregateSum(b.Gid != null ? 1 : 0)
                });

            return query.LeftJoin(subQuery, (a, b) => a.Gid == b.GroupId)
                           .Select((a, b) => new cts_PersonGroup
                           {
                               // 选择需要的字段
                               Gid = a.Gid.SelectAll(),
                               PersonCount = b.PersonCount,
                               PersonCount2 = b.PersonCount2
                           }).OrderByDescending(a => a.Id).ToPagedList(input.page, input.size);

        }
        /// <summary>
        /// 根据地区查询已归档人员分组
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>

        public SqlSugarPagedXList<cts_PersonGroup> SearchAreaPersonGroup(SearchPersonGrouphasPageDto input) {

           return _personGroupRep.AsQueryable()
                .Where(m =>   m.IsSaved == 1 && m.Type == 0 && m.IsShow == 1)
               .Where(m => m.SavedAreaId == UserHelper.GetAreaId())
               .Where(m => m.GroupName.Contains(input.str))
               .WhereIF(!string.IsNullOrEmpty(input.year), m => m.GroupYear == input.year)
               .ToPagedList(input.page,input.size);
          
        }
        /// <summary>
        /// 查询乡镇
        /// </summary>
        /// <param name="groupid"></param>
        /// <returns></returns>
        public List<cts_GroupTown> GetGroupTown(string groupid)
        {
             
            return  _assessPersonRep.AsQueryable()
                .LeftJoin<cts_AssessInfo>((a, b) => a.Gid == b.AssessPeresonId)
                .Where(a => a.GroupId == groupid)
                .GroupBy(a => a.GroupTown)
                .Select((a,b)=>new cts_GroupTown
                {
                    TownName = a.GroupTown,
                    PersonCount = SqlFunc.AggregateCount(1),
                    PersonCount2 = SqlFunc.AggregateSum(b.Gid != null ? 1 : 0)
                }).ToList();
             
        }

        /// <summary>
        /// 更新乡镇状态
        /// </summary>
        /// <param name="input"></param>
        public int UpdateGroupTownStatus(ChangeStatusDto input)
        {
            return _groupTownRep.AsUpdateable().SetColumns(m => m.Status == input.status).Where(m => m.Id == input.id).ExecuteCommand();
        }

        /// <summary>
        /// 保存乡镇
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public bool SaveGroupTown(cts_GroupTown input) {
            if (input.Id > 0)
            {
                return _groupTownRep.Update(input);
            }
            else {  
                return _groupTownRep.Insert(input);
            }
        }
        /// <summary>
        /// 保存项目
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        [UnitOfWork]
        public void SavePersonGroup(cts_PersonGroup input)
        {
            var company = _companySignRep.GetFirst(m => m.Gid == input.CompanyId);
            if (company != null)
            {
                input.CompanyName = company.Name;
                input.CompanySign = company.SignImg;
            }
            if (input.Id > 0)
            {
                _personGroupRep.Update(input);
            }
            else { 
                input.Gid= Guid.NewGuid().ToString();
                _personGroupRep.Insert(input);
            }
            var cAy = _areaYearRep.GetFirst(m => m.YearName == input.GroupYear && m.AreaName == input.GroupArea && m.Type == 0);
            if (cAy==null)
            {
                cAy = new cts_Area_Year
                {
                    YearName = input.GroupYear,
                    AreaName = input.GroupArea,
                    Type = 0
                };
                _areaYearRep.Insert(cAy);
            }
            
        }
        /// <summary>
        /// 获取项目
        /// </summary>
        /// <param name="gid"></param>
        /// <returns></returns>
        public cts_PersonGroup GetPersonGroup(string gid) { 
            return _personGroupRep.GetFirst(m => m.Gid == gid);
        }
        /// <summary>
        /// 导入人员信息
        /// </summary>
        /// <param name="pImport"></param>
        [UnitOfWork]
        public List<ImportPerson> ImportPerson(ImportProp pImport)
        {
            if (pImport == null)
            {
                throw Oops.Bah("参数不能为空");
            }
            if (pImport.pGroup == null)
            {
                throw Oops.Bah("分类不能为空");
            }
            if (pImport.descList == null || pImport.descList.Count <= 0)
            {
                throw Oops.Bah("导入数据不能为空");
            }

            cts_PersonGroup pGroup = pImport.pGroup;

            List<ImportPerson> listError = new List<ImportPerson>();
            List<ImportPerson> listRepeat = new List<ImportPerson>();
            List<cts_AssessPerson> listPerson=new List<cts_AssessPerson>();
            var company = _companySignRep.GetFirst(m => m.Gid == pGroup.CompanyId);
            if (company != null)
            {
                pGroup.CompanyName = company.Name;
                pGroup.CompanySign = company.SignImg;
            }
            if (pGroup.Id == 0)
            {
                pGroup.Gid = Guid.NewGuid().ToString();
                _personGroupRep.Insert(pGroup);
            }
            var cAy = _areaYearRep.GetFirst(m => m.YearName == pGroup.GroupYear && m.AreaName == pGroup.GroupArea && m.Type == 0);
            if (cAy == null)
            {
                cAy = new cts_Area_Year
                {
                    YearName = pGroup.GroupYear,
                    AreaName = pGroup.GroupArea,
                    Type = 0
                };
                _areaYearRep.Insert(cAy);
            }

            foreach (var itemDesc in pImport.descList)
            {
                cts_GroupTown cT = new cts_GroupTown
                {
                    
                    GroupId = pGroup.Gid,
                    TownName = string.IsNullOrEmpty(itemDesc.pSheet) ? "默认" : itemDesc.pSheet
                };

                var town = _groupTownRep.GetFirst(m => m.GroupId == cT.GroupId && m.TownName.Equals(cT.TownName));
                if (town == null)
                {
                    _groupTownRep.Insert(cT);
                }
                for (int i = 0; i < itemDesc.personList.Count; i++)
                {
                    var item = itemDesc.personList[i];
                    var orderindex = i + 1;
                    if (string.IsNullOrEmpty(item.姓名))
                    {
                        item.错误信息 = "信息不全，姓名：" + item.姓名 + ",身份证号：" + item.身份证号 + ",地址：" + item.地址 + item.住址;
                        listError.Add(item);
                        continue;
                    }
                    if (string.IsNullOrEmpty(item.身份证号))
                    {
                        item.错误信息 = "信息不全，姓名：" + item.姓名 + ",身份证号：" + item.身份证号 + ",地址：" + item.地址 + item.住址;
                        listError.Add(item);
                        continue;
                    }
                    if (string.IsNullOrEmpty(item.住址) && string.IsNullOrEmpty(item.地址))
                    {
                        item.错误信息 = "信息不全，姓名：" + item.姓名 + ",身份证号：" + item.身份证号 + ",地址：" + item.地址 + item.住址;
                        listError.Add(item);
                        continue;
                    }

                    item.姓名 = item.姓名.Replace("\n", "").Replace("\t", "").Replace("\r", "").Trim();
                    item.身份证号 = item.身份证号.Replace("\n", "").Replace("\t", "").Replace("\r", "").Replace("'", "").Trim();

                    if (item.身份证号.Length != 15 && item.身份证号.Length != 18)
                    {
                        item.错误信息 = "身份证号格式不正确，姓名：" + item.姓名 + ",身份证号：" + item.身份证号 + ",地址：" + item.地址 + item.住址;
                        listError.Add(item);
                        continue;
                    }
                    var model = _assessPersonRep.AsQueryable().Includes(a => a.AssessInfo)
                        .Where(a => a.IdCard == item.身份证号 && a.IsSaved == 0).First();

                    if (model != null)
                    {
                        var personstate = "未评估";
                        if (model.AssessInfo != null)
                        {
                            personstate = "已评估";
                        }

                        item.错误信息 = "'" + item.姓名 + "' 在'" + model.GroupName + "'中，" + personstate + "！";
                        if (model.GroupId == pGroup.Gid)
                        {
                            continue;
                        }
                        listRepeat.Add(item);
                    }
                    cts_AssessPerson cp = new cts_AssessPerson();
                    cp.Gid = Guid.NewGuid().ToString();
                    cp.Name = item.姓名;
                    cp.Sex = IdCardHelper.GetSexFromIdCard(item.身份证号);
                    cp.Age = IdCardHelper.GetAgeFromIdCard(item.身份证号);
                    cp.Birthday = IdCardHelper.GetBirthDayFromIdCard(item.身份证号);
                    cp.IdCard = item.身份证号;
                    if (!string.IsNullOrEmpty(item.户籍地))
                    {
                        cp.BirthAddress = item.户籍地;
                    }
                    if (!string.IsNullOrEmpty(item.供养方式))
                    {
                        if (item.供养方式.Equals("集中"))
                        {
                            cp.SupportType = 1;
                        }
                        else
                        {
                            cp.SupportType = 2;
                        }
                    }
                    if (!string.IsNullOrEmpty(item.民族))
                    {
                        cp.Nation = item.民族;
                    }


                    cp.OrderIndex = orderindex;
                    if (!string.IsNullOrEmpty(item.住址))
                    {
                        cp.Address = item.住址;
                    }
                    else
                    {
                        cp.Address = item.地址;
                    }

                    cp.GroupDesc = itemDesc.pDesc;
                    cp.GroupTown = itemDesc.pSheet;
                    cp.GroupId = pGroup.Gid;
                    cp.GroupName = pGroup.GroupName;
                    listPerson.Add(cp); 

                }
            }
            if (listError.Count > 0)
            {
                throw Oops.Bah("操作失败").WithData(listError);
            }
            else
            {
                _assessPersonRep.InsertRange(listPerson);
                return listRepeat;
            }

        }

        /// <summary>
        /// 导入人员评估报告
        /// </summary>
        /// <param name="pImport"></param>
        [UnitOfWork]
        public List<ImportPerson> ImportPersonRecord(ImportProp pImport)
        {
            if (pImport == null)
            {
                throw Oops.Bah("参数不能为空");
            }
            if (pImport.pGroup == null)
            {
                throw Oops.Bah("分类不能为空");
            }
            if (pImport.descList == null || pImport.descList.Count <= 0)
            {
                throw Oops.Bah("导入数据不能为空");
            }

            cts_PersonGroup pGroup = pImport.pGroup;

            List<ImportPerson> listError = new List<ImportPerson>();
            List<ImportPerson> listRepeat = new List<ImportPerson>();
            List<cts_AssessPerson> listPerson = new List<cts_AssessPerson>();
            List<cts_AssessInfo> listInfo = new List<cts_AssessInfo>();
            var company = _companySignRep.GetFirst(m => m.Gid == pGroup.CompanyId);
            if (company != null)
            {
                pGroup.CompanyName = company.Name;
                pGroup.CompanySign = company.SignImg;
            }
            if (pGroup.Id == 0)
            {
                pGroup.Gid = Guid.NewGuid().ToString();
                _personGroupRep.Insert(pGroup);
            }
            var cAy = _areaYearRep.GetFirst(m => m.YearName == pGroup.GroupYear && m.AreaName == pGroup.GroupArea && m.Type == 0);
            if (cAy == null)
            {
                cAy = new cts_Area_Year
                {
                    YearName = pGroup.GroupYear,
                    AreaName = pGroup.GroupArea,
                    Type = 0
                };
                _areaYearRep.Insert(cAy);
            }

            foreach (var itemDesc in pImport.descList)
            {
                cts_GroupTown cT = new cts_GroupTown
                {
                    
                    GroupId = pGroup.Gid,
                    TownName = string.IsNullOrEmpty(itemDesc.pSheet) ? "默认" : itemDesc.pSheet
                };

                var town = _groupTownRep.GetFirst(m => m.GroupId == cT.GroupId && m.TownName.Equals(cT.TownName));
                if (town == null)
                {
                    _groupTownRep.Insert(cT);
                }
                for (int i = 0; i < itemDesc.personList.Count; i++)
                {
                    var item = itemDesc.personList[i];
                    var orderindex = i + 1;
                    if (string.IsNullOrEmpty(item.姓名))
                    {
                        item.错误信息 = "信息不全，姓名：" + item.姓名 + ",身份证号：" + item.身份证号 + ",地址：" + item.地址 + item.住址;
                        listError.Add(item);
                        continue;
                    }
                    if (string.IsNullOrEmpty(item.身份证号))
                    {
                        item.错误信息 = "信息不全，姓名：" + item.姓名 + ",身份证号：" + item.身份证号 + ",地址：" + item.地址 + item.住址;
                        listError.Add(item);
                        continue;
                    }
                    if (string.IsNullOrEmpty(item.住址) && string.IsNullOrEmpty(item.地址))
                    {
                        item.错误信息 = "信息不全，姓名：" + item.姓名 + ",身份证号：" + item.身份证号 + ",地址：" + item.地址 + item.住址;
                        listError.Add(item);
                        continue;
                    }

                    item.姓名 = item.姓名.Replace("\n", "").Replace("\t", "").Replace("\r", "").Trim();
                    item.身份证号 = item.身份证号.Replace("\n", "").Replace("\t", "").Replace("\r", "").Replace("'", "").Trim();

                    if (item.身份证号.Length != 15 && item.身份证号.Length != 18)
                    {
                        item.错误信息 = "身份证号格式不正确，姓名：" + item.姓名 + ",身份证号：" + item.身份证号 + ",地址：" + item.地址 + item.住址;
                        listError.Add(item);
                        continue;
                    }
                    var model = _assessPersonRep.AsQueryable().Includes(a => a.AssessInfo)
                        .Where(a => a.IdCard == item.身份证号 && a.IsSaved == 0).First();

                    if (model != null)
                    {
                        var personstate = "未评估";
                        if (model.AssessInfo != null)
                        {
                            personstate = "已评估";
                        }

                        item.错误信息 = "'" + item.姓名 + "' 在'" + model.GroupName + "'中，" + personstate + "！";
                        if (model.GroupId == pGroup.Gid)
                        {
                            continue;
                        }
                        listRepeat.Add(item);
                    }
                    item.姓名 = item.姓名.Replace("\n", "").Replace("\t", "").Replace("\r", "").Trim();
                    item.身份证号 = item.身份证号.Replace("\n", "").Replace("\t", "").Replace("\r", "").Replace("'", "").Trim();
                    orderindex += 1;
                    cts_AssessPerson cp = new cts_AssessPerson();
                    cp.Gid = Guid.NewGuid().ToString();
                    cp.Name = item.姓名 + "(导)";
                    cp.Sex = IdCardHelper.GetSexFromIdCard(item.身份证号);
                    cp.Age = IdCardHelper.GetAgeFromIdCard(item.身份证号);
                    cp.Birthday = IdCardHelper.GetBirthDayFromIdCard(item.身份证号);
                    cp.IdCard = item.身份证号;
                    cp.OrderIndex = orderindex;
                    cp.Remark = item.备注;
                    if (!string.IsNullOrEmpty(item.地址))
                    {
                        cp.Address = item.地址;
                    }
                    else
                    {
                        cp.Address = item.住址;
                    }
                    cp.GroupDesc = "人工导入评估结果";
                    cp.GroupTown = itemDesc.pSheet;
                    cp.GroupId = pGroup.Gid;
                    cp.GroupName = pGroup.GroupName;
                    DateTime dtcreate = DateTime.Now;
                    try
                    {
                        string dtstr = pGroup.GroupYear.Replace("年度", "");
                        dtcreate = DateTime.Parse(dtstr + "-01-01");
                    }
                    catch (Exception ex)
                    {
                        dtcreate = DateTime.Parse("2018-01-01");
                    }

                    cp.sys_CreateDate = dtcreate;
                    cp.IsSaved = 1;
                    listPerson.Add(cp);

                    if (!string.IsNullOrEmpty(item.评估结果))
                    {
                        cts_AssessInfo ca = new cts_AssessInfo();
                        ca.sys_CreateDate = dtcreate;
                        ca.BusinessDate = dtcreate;
                        ca.sys_ModifyDate = DateTime.Now;
                        ca.sys_Creater = UserHelper.GetUserId();
                        ca.sys_CreaterName = UserHelper.GetUserName();
                        ca.AssessPeresonId = cp.Gid;
                        ca.IsSaved = 1;
                        ca.AssessType = 0;

                        string level = item.评估结果.Replace(" ", "");
                        if (level.Equals("自理") || level.Equals("能力完好"))
                        {
                            ca.FinalLevel = 0;
                        }
                        else if (level.Equals("轻度失能"))
                        {
                            ca.FinalLevel = 1;
                            ca.Remark = level;
                        }
                        else if (level.Equals("半失能") || level.Equals("半自理") || level.Equals("中度失能"))
                        {
                            ca.FinalLevel = 2;
                            ca.Remark = level;
                        }
                        else if (level.Equals("失能") || level.Equals("重度失能"))
                        {
                            ca.FinalLevel = 3;
                        }
                        else
                        {
                            ca.FinalLevel = 0;
                            ca.Remark = level;
                        }
                        ca.FirstLevel = ca.FinalLevel;
                        listInfo.Add(ca);
                    }
                }


            }
            if (listError.Count > 0)
            {
                throw Oops.Bah("操作失败").WithData(listError);
            }
            else
            {
                _assessPersonRep.InsertRange(listPerson);
                _assessInfoRep.InsertRange(listInfo);
                return listRepeat;
            }
        }

        /// <summary>
        /// 查询人员
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public SqlSugarPagedXList<cts_AssessPerson> SearchPerson(SearchPersonDto input)
        {

            return _assessPersonRep.AsQueryable()
           .LeftJoin<cts_AssessInfo>((a, b) => a.Gid == b.AssessPeresonId) 
           .WhereIF(!string.IsNullOrEmpty(input.GroupId), a => a.GroupId.Equals(input.GroupId))
           .WhereIF(!string.IsNullOrEmpty(input.GroupTown), a => a.GroupTown.Equals(input.GroupTown))
           .WhereIF(input.State == 0, (a, b) => a.State == 0 && b.Id == null)
           .WhereIF(input.State == 1, (a, b) => b.Id != null)
           .WhereIF(input.State == 2, (a, b) => a.State == 2 && b.Id == null)
           .WhereIF(!string.IsNullOrEmpty(input.Area), (a, b) => b.AssessArea.Contains(input.Area))
           .WhereIF(!string.IsNullOrEmpty(input.Township), (a, b) => b.AssessTownship.Contains(input.Township))
           .WhereIF(input.IsNew == 1, a => a.IsNew == 1)
           .WhereIF(!string.IsNullOrEmpty(input.Str), a => a.Name.Contains(input.Str) || a.Address.Contains(input.Str) || a.IdCard.Contains(input.Str))
           .WhereIF(UserHelper.GetAuth() != (UserHelper.GetAuth() | 2) , (a, b) => b.Id == null||    b.sys_Creater == UserHelper.GetUserId())
          .Select((a,b)=>new cts_AssessPerson { 
            Id=a.Id.SelectAll(),
            AssessInfo=b
          })
           
           .ToPagedList(input.page, input.size);
 
        }
        /// <summary>
        /// 根据人员gid获取评估信息
        /// </summary>
        /// <param name="Gid"></param>
        /// <returns></returns>
        public cts_AssessPerson GetAssessInfoByPersonId(string Gid) { 
        
            return _assessPersonRep.AsQueryable().Includes(a=>a.AssessInfo,b=>b.AssessImgs).Where(a=>a.Gid==Gid).First();
        }

        /// <summary>
        /// 查询人员信息，没有就新增
        /// </summary>
        /// <param name="info"></param>
        /// <returns></returns>
        public cts_AssessPerson SearchOrSavePerson(cts_AssessPerson info)
        {
            info.IdCard = info.IdCard.Trim();
            if (info.IdCard.Length != 15 && info.IdCard.Length != 18)
            {
                throw Oops.Bah("身份证号格式不正确");
            }
            info.IsNew = 1;
            if (string.IsNullOrEmpty(info.GroupId))
            {
                info.GroupId = AppStr.NEWGROUPID;
                info.GroupName = AppStr.NEWGROUPNAME;
            }
            var model = _assessPersonRep.GetFirst(m => m.IdCard == info.IdCard && m.GroupId == info.GroupId);
            if (model != null)
            {
                throw Oops.Bah("该项目中此身份证号已存在");
            }


            var orderIndex = _assessPersonRep.AsQueryable()
                                 .Where(m => m.GroupId == info.GroupId && m.GroupTown == info.GroupTown)
                                 .Max(m => m.OrderIndex);
            info.OrderIndex =( orderIndex+1);
            info.Gid = Guid.NewGuid().ToString();
            info.Age = IdCardHelper.GetAgeFromIdCard(info.IdCard);
            info.Birthday = IdCardHelper.GetBirthDayFromIdCard(info.IdCard);
            _assessPersonRep.Insert(info);
            return info;
        }

        /// <summary>
        /// 根据身份证号查询人员是否已被评估
        /// </summary>
        /// <param name="idcardno"></param>
        /// <returns></returns>
        public bool GetHasAssessByIdcard(string idcardno)
        {
            return true;
        }
        /// <summary>
        /// 根据id查询评估对象信息
        /// </summary>
        /// <param name="gid"></param>
        /// <returns></returns>
        public cts_AssessPerson GetPersonById(string gid) { 
            return  _assessPersonRep.GetFirst(m=>m.Gid == gid);
        }

        /// <summary>
        /// 根据身份证号查询上次评估结果
        /// </summary>
        /// <param name="idcard"></param>
        /// <returns></returns>
        public string GetAccessInfoByIdCardNo(string idcard)
        {

            var person = _assessPersonRep.AsQueryable().Includes(a => a.AssessInfo)
               .Where(a => a.IdCard == idcard && a.IsSaved == 1).OrderByDescending(a => a.sys_CreateDate)
                .First();

            if (person != null && person.AssessInfo != null && person.AssessInfo.Gid != null)
            {
                var levelStr = Util.GetLevelStr(person.AssessInfo.AssessType, person.AssessInfo.FinalLevel);
                var msg = person.Name + "，上次评估结果为：" + levelStr;
                if (!string.IsNullOrEmpty(person.AssessInfo.Remark))
                {
                    msg += ",备注：" + person.AssessInfo.Remark;
                }

                return msg;
            }

            return "";
        }
        /// <summary>
        /// 保存评估对象信息
        /// </summary>
        /// <param name="info"></param>
        [HttpPost]
        [UnitOfWork]
        public void SavePerson(cts_AssessPerson info)
        {
            if (info == null)
            {
                throw Oops.Bah("保存对象不能为空");
            }
            if (string.IsNullOrEmpty(info.Name))
            {
                throw Oops.Bah("姓名不能为空");
            }
            if (string.IsNullOrEmpty(info.Sex))
            {
                throw Oops.Bah("姓别不能为空");
            }
            if (string.IsNullOrEmpty(info.IdCard))
            {
                throw Oops.Bah("身份证号不能为空");
            }
            if (string.IsNullOrEmpty(info.Address))
            {
                throw Oops.Bah("住址不能为空");
            }
            //新增

            if (string.IsNullOrEmpty(info.Gid))
            {

                var model = _assessPersonRep.AsQueryable() 
             .Where(a => a.IdCard == info.IdCard && a.IsSaved == 0 && a.GroupId == info.GroupId).First();
                 
                if (model != null)
                {
                    throw Oops.Bah("该项目中此身份证号已存在");
                }
                info.Gid = Guid.NewGuid().ToString();
                info.Age = IdCardHelper.GetAgeFromIdCard(info.IdCard);
                info.Birthday = IdCardHelper.GetBirthDayFromIdCard(info.IdCard);
                info.Sex = IdCardHelper.GetSexFromIdCard(info.IdCard);


                var orderIndex = _assessPersonRep.AsQueryable()
                                     .Where(m => m.GroupId == info.GroupId && m.GroupTown == info.GroupTown)
                                     .Max(m => m.OrderIndex);
                info.OrderIndex = (orderIndex + 1);
                _assessPersonRep.Insert(info);
              
            }
            else
            {
                var model = _assessPersonRep.AsQueryable()
                   .Where(a => a.IdCard == info.IdCard && a.IsSaved == 0 && a.GroupId == info.GroupId && a.Gid != info.Gid).First();

                
                if (model != null)
                {
                    throw Oops.Bah("该项目中此身份证号已存在");
                }
                if (!info.GroupId.Equals(AppStr.NEWGROUPID) && info.OrderIndex == 0)
                {
                    var orderIndex = _assessPersonRep.AsQueryable()
                                      .Where(m => m.GroupId == info.GroupId && m.GroupTown == info.GroupTown)
                                      .Max(m => m.OrderIndex);
                    info.OrderIndex = (orderIndex + 1);
                }
                info.Age = IdCardHelper.GetAgeFromIdCard(info.IdCard);
                info.Birthday = IdCardHelper.GetBirthDayFromIdCard(info.IdCard);
                info.Sex = IdCardHelper.GetSexFromIdCard(info.IdCard);

                _assessPersonRep.Update(info);
            }

            cts_GroupTown cT = new cts_GroupTown
            {
               
                GroupId = info.GroupId,
                TownName = info.GroupTown
            };
            var town = _groupTownRep.GetFirst(m => m.GroupId == cT.GroupId && m.TownName.Equals(cT.TownName));
            if (town == null)
            {
                _groupTownRep.Insert(cT);
            }

        }
        /// <summary>
        /// 转移人员到项目
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        [UnitOfWork]    
        public void TransPersonGroup(TransPersonInput input)
        {
             var person=_assessPersonRep.GetFirst(m=>m.Gid==input.personid);
            if (person == null)
            {
                throw Oops.Bah("未找到此评估对象");
            }
            person.GroupId =input. groupid;
            person.GroupName = input .groupname;
            person.GroupTown = input.grouptown;

            var orderIndex = _assessPersonRep.AsQueryable()
                                     .Where(m => m.GroupId == person.GroupId && m.GroupTown == person.GroupTown)
                                     .Max(m => m.OrderIndex);
            person.OrderIndex = (orderIndex + 1);
            _assessPersonRep.AsUpdateable(person).UpdateColumns(m=>new { 
                m.GroupDesc,
                m.GroupName,
                m.GroupTown,
                m.OrderIndex,

            }).ExecuteCommand();
            cts_GroupTown cT = new cts_GroupTown
            {
                
                GroupId = person.GroupId,
                TownName = person.GroupTown
            };
            var town = _groupTownRep.GetFirst(m => m.GroupId == cT.GroupId && m.TownName.Equals(cT.TownName));
            if (town == null)
            {
                _groupTownRep.Insert(cT);
            }
        }

        /// <summary>
        /// 保存评估人员备注
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns> 
        public void SavePersonRemark(SavePersonRemarkInput input)
        {
            var person = _assessPersonRep.GetFirst(m => m.Gid == input.gid);
            if (person == null)
            {
                throw Oops.Bah("未找到此评估对象");
            }
            person.Remark = input.remark;
            person.State = input.state; 
            
            _assessPersonRep.AsUpdateable(person).UpdateColumns(m => new {
                m.Remark,
                m.State,  
            }).ExecuteCommand();
           
        }

        /// <summary>
        /// 删除评估对象
        /// </summary>
        /// <param name="gid"></param>
        [UnitOfWork]
        public void DeletePerson(string gid)
        { 
            _assessPersonRep.AsUpdateable().SetColumns(m=>m.sys_IsDelete==1).Where(m=>m.Gid==gid).ExecuteCommand();
            _assessInfoRep.AsUpdateable().SetColumns(m => m.sys_IsDelete == 1).Where(m => m.AssessPeresonId == gid).ExecuteCommand();

        }
        /// <summary>
        /// 删除评估项目
        /// </summary>
        /// <param name="gid"></param>
        [UnitOfWork]
        public void DeletePersonGroup(string gid)
        {
           
            _assessPersonRep.AsUpdateable().SetColumns(m => m.sys_IsDelete == 1).Where(m => m.GroupId == gid).ExecuteCommand();
            _personGroupRep.AsUpdateable().SetColumns(m => m.sys_IsDelete == 1).Where(m => m.Gid == gid).ExecuteCommand();

        }
        /// <summary>
        /// 存档评估项目
        /// </summary>
        /// <param name="input"></param>
        [UnitOfWork]
        public void SavedPersonGroup(SavedPersonGroupInput input)
        {

            _personGroupRep.AsUpdateable().SetColumns(m => new cts_PersonGroup { SavedAreaId = input.areaid, IsSaved = 1 }).Where(m => m.Gid == input.gid).ExecuteCommand();
            _assessPersonRep.AsUpdateable().SetColumns(m => m.IsSaved == 1).Where(m => m.GroupId == input.gid).ExecuteCommand();
            _assessInfoRep.AsUpdateable().SetColumns(m => m.IsSaved == 1).Where(m => SqlFunc.Subqueryable<cts_AssessPerson>()
               .Where(p => p.GroupId == input.gid)
               .Select(p => p.Gid)
               .Contains(m.AssessPeresonId)).ExecuteCommand();

        }
        /// <summary>
        /// 取消存档
        /// </summary>
        /// <param name="gid"></param>
        [UnitOfWork]
        public void CancelSavedPersonGroup(string gid)
        {

            _personGroupRep.AsUpdateable().SetColumns(m => new cts_PersonGroup { SavedAreaId = "", IsSaved = 0 }).Where(m => m.Gid == gid).ExecuteCommand();
            _assessPersonRep.AsUpdateable().SetColumns(m => m.IsSaved == 0).Where(m => m.GroupId == gid).ExecuteCommand();
            _assessInfoRep.AsUpdateable().SetColumns(m => m.IsSaved == 0).Where(m => SqlFunc.Subqueryable<cts_AssessPerson>()
             .Where(p => p.GroupId == gid)
             .Select(p => p.Gid)
             .Contains(m.AssessPeresonId)).ExecuteCommand();

        }

        /// <summary>
        /// 同步签名
        /// </summary>
        /// <param name="gid"></param>
        public void SyncEmplyeeSign(string gid) {

            var sqlStr = $@"UPDATE cts_AssessInfo 
SET EmployeeName1 = b.SignImg1,
EmployeeName2 = b.SignImg2 
FROM
	cts_AssessInfo a
	JOIN cts_Employee b ON a.sys_Creater = b.Gid 
WHERE
	a.AssessPeresonId IN ( SELECT gid FROM cts_AssessPerson WHERE GroupId = '{gid}' );"; 

            _assessInfoRep.AsSugarClient().Ado.ExecuteCommand(sqlStr);
        }

        /// <summary>
        /// 保存评估信息
        /// </summary>     
        /// <returns></returns>
        [UnitOfWork]
        public void SaveAssessInfo(cts_AssessInfo info) {

            var cEmp = _employeeRep.GetFirst(m => m.Gid == UserHelper.GetUserId());
            if (string.IsNullOrEmpty(info.Gid))
            {

                cts_AssessInfo vInfo = _assessInfoRep.AsQueryable().Where(m => m.AssessPeresonId == info.AssessPeresonId && m.IsSaved == 0).First();
                if (vInfo != null)
                {
                    throw Oops.Bah("该人员已被评估");
                }


                info.Gid = Guid.NewGuid().ToString();
                info.State = 1;
                info.sys_CreateDate = DateTime.Now;
                info.sys_Creater = cEmp.Gid;
                info.sys_CreaterName = cEmp.EmployeeName;
                info.EmployeeName1 = cEmp.SignImg1;
                info.EmployeeName2 = cEmp.SignImg2;
                _assessInfoRep.Insert(info);


                foreach (var item in info.Imgs)
                {
                    cts_AssessImg img = new cts_AssessImg();
                    img.Gid = Guid.NewGuid().ToString();
                    img.AssessId = info.Gid;
                    img.ImageUrl = item;
                    img.Type = 0;
                    _assessImgRep.Insert(img);

                }
            }
            else
            {

                cts_AssessInfo oldInfo = _assessInfoRep.AsQueryable().Where(m => m.Gid == info.Gid).First();
                if (cEmp.Auth != (cEmp.Auth | 1) && !cEmp.Gid.Equals(oldInfo.sys_Creater))
                {
                    throw Oops.Bah("无权限");
                }
                info.sys_ModifyDate = DateTime.Now;
                info.sys_Modifier = cEmp.Gid;
                info.sys_ModifierName = cEmp.EmployeeName;
                _assessInfoRep.Update(info);
                _assessImgRep.Delete(m => m.AssessId == info.Gid);
                foreach (var item in info.Imgs)
                {
                    cts_AssessImg img = new cts_AssessImg();
                    img.Gid = Guid.NewGuid().ToString();
                    img.AssessId = info.Gid;
                    img.ImageUrl = item;
                    img.Type = 0;
                    _assessImgRep.Insert(img);

                }
                //保存修改记录
                AssessUpdateUtil uu = new AssessUpdateUtil();
                Dictionary<string, string> dicFields = uu.GetAssessUpdateFields();
                Type type = info.GetType();
                List<cts_AssessUpdate> updates = new List<cts_AssessUpdate>();
                var seq = Guid.NewGuid().ToString();
                foreach (PropertyInfo p in type.GetProperties())
                {
                    string tmpValue = "";
                    if (dicFields.TryGetValue(p.Name, out tmpValue))
                    {
                        cts_AssessUpdate uptemp = new cts_AssessUpdate();
                        var oldValue = p.GetValue(oldInfo);
                        var newValue = p.GetValue(info);
                        if (oldValue == null)
                        {
                            oldValue = "";
                        }
                        if (newValue == null)
                        {
                            newValue = "";
                        }
                        if (!oldValue.ToString().Equals(newValue.ToString()))
                        {
                            if (p.GetType() == typeof(int))
                            {
                                if (oldValue.ToString().Equals("-1"))
                                {
                                    uptemp.OldFiled = "";
                                }
                                else
                                {
                                    uptemp.OldFiled = oldValue.ToString();
                                }
                            }
                            else
                            {
                                uptemp.OldFiled = oldValue.ToString();
                            }
                            uptemp.AssessId = oldInfo.Gid;
                            uptemp.NewFiled = newValue.ToString();
                            uptemp.FileAction = "修改";
                            uptemp.FiledName = tmpValue;
                            uptemp.Type = oldInfo.IsSaved;
                            uptemp.Seq = seq;
                            uptemp.sys_CreateDate = DateTime.Now;
                            uptemp.sys_Creater = cEmp.Gid;
                            uptemp.sys_CreaterName = cEmp.EmployeeName;
                            _assessUpdateRep.Insert(uptemp);

                        }
                    }
                }

            }

        }

        #endregion

        #region 统计报表
        /// <summary>
        /// 获取报表
        /// </summary>
        /// <returns></returns>
        public dynamic GetCount() {
            var sqlStr = $@"select * from(
                             select COUNT(a.Id) as count1 from cts_AssessPerson a left join cts_AssessInfo b on a.Gid=b.AssessPeresonId
                             where a.IsSaved=0 and a.sys_IsDelete=0 and b.Id is null and a.GroupId!='A07606F6-BAEB-493F-A3C7-8E2C6525DF91') a
                             left join(
                             select COUNT(a.Id) as count2 from cts_AssessPerson a left join cts_AssessInfo b on a.Gid=b.AssessPeresonId
                             where a.IsSaved=0 and a.sys_IsDelete=0 and b.Id is not null) b
                             on 1=1
                            left join (
                             select  COUNT(a.Id) as count3 from cts_AssessPerson a inner join cts_AssessInfo b on a.Gid=b.AssessPeresonId where a.IsSaved=0 and a.sys_IsDelete=0 and a.IsNew=1) c
                             on 1=1
 
                            left join (
                             select  COUNT(a.Id) as count4 from cts_DBPerson a left join cts_DBInfo b on a.Gid=b.DBPersonId
                             where a.IsSaved=0 and a.sys_IsDelete=0 and b.Id is null and a.GroupId!='A07606F6-BAEB-493F-A3C7-8E2C6525DF91') d
                             on 1=1

                            left join (
                             select COUNT(a.Id) as count5 from cts_DBPerson a inner join cts_DBInfo b on a.Gid=b.DBPersonId
                             where a.IsSaved=0 and a.sys_IsDelete=0 and b.Id is not null) e
                             on 1=1

                            left join (
                            select  COUNT(a.Id) as count6 from cts_DBPerson a inner join cts_DBInfo b on a.Gid=b.DBPersonId where a.IsSaved=0 and a.sys_IsDelete=0 and a.IsNew=1) f
                             on 1=1";
           return  _assessPersonRep.AsSugarClient().SqlQueryable<dynamic>(sqlStr).First();
         
        }

        /// <summary>
        /// 统计数据
        /// </summary>
        /// <param name="year"></param>
        /// <param name="month"></param>
        /// <returns></returns>
        public dynamic GetWorkCount(string year, string month) {
            string dateStr1, dateStr2;
            var cEmp = _employeeRep.GetFirst(m => m.Gid == UserHelper.GetUserId());
            if (month.Equals("13"))
            {
                dateStr1 = year + "-01-01";
                dateStr2 = year + "-12-31";
            }
            else
            {
                month = month.PadLeft(2, '0');
                string dateStr = year + "-" + month + "-01";
                DateTime date1 = Convert.ToDateTime(dateStr);
                DateTime date2 = Util.LastDayOfMonth(date1);
                dateStr1 = date1.ToString("yyyy-MM-dd");
                dateStr2 = date2.ToString("yyyy-MM-dd");
            }

            if (cEmp.Auth != (cEmp.Auth | 2))
            {
                var sql0 = "select Gid as EmployeeId,EmployeeName,EmployeeAvator,Type from cts_Employee where Type=0";
                //身体能力评估 0.5 积分/人
                var sql1 = $@"SELECT A.sys_Creater as EmployeeId,
                                            A.sys_CreaterName as EmployeeName,
                                             cast(COUNT(0)*1./2 AS decimal(18,
                                            1)) AS AssessCount
                                    FROM cts_AssessInfo A
                                    INNER JOIN cts_AssessPerson B
                                        ON A.AssessPeresonId=B.Gid
                                    WHERE A.IsDelete=0
                                            AND B.IsDelete=0
                                            AND A.sys_Creater='{cEmp.Gid}' 
                                            AND DATEDIFF(DAY,'{dateStr1}',A.sys_CreateDate)>=0
                                            AND DATEDIFF(DAY,A.sys_CreateDate,'{dateStr2}')>=0
                                    GROUP BY  A.sys_Creater,A.sys_CreaterName ";
                //低保评估 0.5 积分/人,核查信息
                var sql2 = $@"SELECT A.sys_Creater as EmployeeId,
                                            A.sys_CreaterName as EmployeeName,
                                                cast(COUNT(0)*1./2 AS decimal(18,
                                                1)) AS DBCount
                                        FROM cts_DBInfo A
                                        INNER JOIN cts_DBPerson B
                                            ON A.DBPersonId=B.Gid
                                        WHERE A.sys_IsDelete=0 
                                                AND B.sys_IsDelete=0
                                                AND A.sys_Creater='{cEmp.Gid}' 
                                                AND DATEDIFF(DAY,'{dateStr1}',A.sys_CreateDate)>=0
                                                AND DATEDIFF(DAY,A.sys_CreateDate,'{dateStr2}')>=0
                                        GROUP BY  A.sys_Creater,A.sys_CreaterName ";
                //低保评估 0.5 积分/人,上传照片
                var sql3 = $@"SELECT sys_Creater as EmployeeId,
                                            sys_CreaterName as EmployeeName,
                                         SUM(CAST(0.5 / PersoncCount AS decimal(18,
                                         2))) AS DBCount from
                                    (SELECT DISTINCT C.sys_Creater,
                                        C.sys_CreaterName,
                                        C.DBInfoId
                                    FROM cts_DBInfo A
                                    INNER JOIN cts_DBPerson B
                                        ON A.DBPersonId=B.Gid
                                    INNER JOIN cts_DBInfoImg C
                                        ON B.Gid=C.DBInfoId
                                    WHERE A.sys_IsDelete=0
                                            AND B.sys_IsDelete=0
                                            AND C.sys_Creater='{cEmp.Gid}' 
                                            AND C.Type<7
                                            AND DATEDIFF(DAY,'{dateStr1}',A.sys_CreateDate)>=0
                                            AND DATEDIFF(DAY,A.sys_CreateDate,'{dateStr2}')>=0
                                            AND DATEDIFF(DAY,'{dateStr1}',C.sys_CreateDate)>=0
                                            AND DATEDIFF(DAY,C.sys_CreateDate,'{dateStr2}')>=0 )A
                                INNER JOIN 
                                    (SELECT DBInfoId,
                                        COUNT(0) AS PersoncCount FROM
                                        (SELECT DISTINCT C.sys_Creater,
                                        C.sys_CreaterName,
                                        C.DBInfoId
                                        FROM cts_DBInfo A
                                        INNER JOIN cts_DBPerson B
                                            ON A.DBPersonId=B.Gid
                                        INNER JOIN cts_DBInfoImg C
                                            ON B.Gid=C.DBInfoId
                                        WHERE A.sys_IsDelete=0
                                                AND B.sys_IsDelete=0 
                                                AND C.Type<7
                                                AND DATEDIFF(DAY,'{dateStr1}',A.sys_CreateDate)>=0
                                                AND DATEDIFF(DAY,A.sys_CreateDate,'{dateStr2}')>=0
                                                AND DATEDIFF(DAY,'{dateStr1}',C.sys_CreateDate)>=0
                                                AND DATEDIFF(DAY,C.sys_CreateDate,'{dateStr2}')>=0 ) t
                                        GROUP BY  DBInfoId ) B
                                        ON A.DBInfoId=B.DBInfoId
                                GROUP BY  sys_Creater,sys_CreaterName";
                //救助评估 0.5 积分/人,核查信息
                var sql4 = $@"SELECT A.sys_Creater as EmployeeId,
                                            A.sys_CreaterName as EmployeeName,
                                        cast(COUNT(0)*1./2 AS decimal(18,
                                        1)) AS JZCount
                                FROM cts_JZInfo A
                                INNER JOIN cts_JZPerson B
                                    ON A.DBPersonId=B.Gid
                                WHERE A.sys_IsDelete=0
                                        AND B.sys_IsDelete=0
                                        AND A.sys_Creater='{cEmp.Gid}' 
                                        AND DATEDIFF(DAY,'{dateStr1}',A.sys_CreateDate)>=0
                                        AND DATEDIFF(DAY,A.sys_CreateDate,'{dateStr2}')>=0
                                GROUP BY  A.sys_Creater,A.sys_CreaterName ";

                //救助评估 0.5 积分,上传图片
                var sql5 = $@"SELECT sys_Creater as EmployeeId,
                                            sys_CreaterName as EmployeeName,
                                         SUM(CAST(0.5 / PersoncCount AS decimal(18,
                                         2))) AS JZCount from
                                    (SELECT DISTINCT C.sys_Creater,
                                        C.sys_CreaterName,
                                        C.DBInfoId
                                    FROM cts_JZInfo A
                                    INNER JOIN cts_JZPerson B
                                        ON A.DBPersonId=B.Gid
                                    INNER JOIN cts_JZInfoImg C
                                        ON B.Gid=C.DBInfoId
                                    WHERE A.sys_IsDelete=0
                                            AND B.sys_IsDelete=0
                                            AND C.sys_Creater='{cEmp.Gid}' 
                                            AND C.Type<7
                                            AND DATEDIFF(DAY,'{dateStr1}',A.sys_CreateDate)>=0
                                            AND DATEDIFF(DAY,A.sys_CreateDate,'{dateStr2}')>=0
                                            AND DATEDIFF(DAY,'{dateStr1}',C.sys_CreateDate)>=0
                                            AND DATEDIFF(DAY,C.sys_CreateDate,'{dateStr2}')>=0 )A
                                INNER JOIN 
                                    (SELECT DBInfoId,
                                        COUNT(0) AS PersoncCount FROM
                                        (SELECT DISTINCT C.sys_Creater,
                                        C.sys_CreaterName,
                                        C.DBInfoId
                                        FROM cts_JZInfo A
                                        INNER JOIN cts_JZPerson B
                                            ON A.DBPersonId=B.Gid
                                        INNER JOIN cts_JZInfoImg C
                                            ON B.Gid=C.DBInfoId
                                        WHERE A.sys_IsDelete=0
                                                AND B.sys_IsDelete=0 
                                                AND C.Type<7
                                                AND DATEDIFF(DAY,'{dateStr1}',A.sys_CreateDate)>=0
                                                AND DATEDIFF(DAY,A.sys_CreateDate,'{dateStr2}')>=0
                                                AND DATEDIFF(DAY,'{dateStr1}',C.sys_CreateDate)>=0
                                                AND DATEDIFF(DAY,C.sys_CreateDate,'{dateStr2}')>=0 ) t
                                        GROUP BY  DBInfoId ) B
                                        ON A.DBInfoId=B.DBInfoId
                                GROUP BY  sys_Creater,sys_CreaterName";

                //日间照料中心 4 积分
                var sql6 = $@"SELECT 
                                            B.sys_Creater as EmployeeId,
                                            B.sys_CreaterName as EmployeeName,
		                                 SUM(
                                         CAST(4.0 / C.PersonCount AS decimal(18,
                                         2))) AS RijianCount
                                FROM dbo.cts_AssessRijian AS A
                                INNER JOIN 
                                    (SELECT DISTINCT OrgId,
                                         sys_Creater,
                                         sys_CreaterName
                                    FROM dbo.cts_RijianAssessInfo WHERE sys_Creater='{cEmp.Gid}' AND   DATEDIFF(DAY,'{dateStr1}',sys_CreateDate)>=0 AND DATEDIFF(DAY,sys_CreateDate,'{dateStr2}')>=0) AS B
                                    ON A.Gid = B.OrgId
                                INNER JOIN 
                                    (SELECT OrgId,
                                         COUNT(0) AS PersonCount
                                    FROM 
                                        (SELECT DISTINCT OrgId,
                                         sys_Creater,
                                         sys_CreaterName
                                        FROM dbo.cts_RijianAssessInfo  WHERE   DATEDIFF(DAY,'{dateStr1}',sys_CreateDate)>=0 AND DATEDIFF(DAY,sys_CreateDate,'{dateStr2}')>=0 ) AS t
                                        GROUP BY  OrgId) AS C
                                        ON A.Gid = C.OrgId
                                WHERE (A.Name <> '' AND A.sys_IsDelete=0)
                                GROUP BY B.sys_Creater,B.sys_CreaterName";

                //幸福院 2 积分
                var sql7 = $@" SELECT  
                                         B.sys_Creater as EmployeeId,
                                            B.sys_CreaterName as EmployeeName,
		                                 SUM(
                                         CAST(2.0 / C.PersonCount AS decimal(18,
                                         2))) AS XFYCount
                                FROM dbo.cts_AssessXFY AS A
                                INNER JOIN 
                                    (SELECT DISTINCT XFYId,
                                         sys_Creater,
                                         sys_CreaterName
                                    FROM dbo.cts_XFYAssessInfo WHERE sys_Creater='{cEmp.Gid}'  AND DATEDIFF(DAY,'{dateStr1}',sys_CreateDate)>=0 AND DATEDIFF(DAY,sys_CreateDate,'{dateStr2}')>=0) AS B
                                    ON A.Gid = B.XFYId
                                INNER JOIN 
                                    (SELECT XFYId,
                                         COUNT(0) AS PersonCount
                                    FROM 
                                        (SELECT DISTINCT XFYId,
                                         sys_Creater,
                                         sys_CreaterName
                                        FROM dbo.cts_XFYAssessInfo  WHERE  DATEDIFF(DAY,'{dateStr1}',sys_CreateDate)>=0 AND DATEDIFF(DAY,sys_CreateDate,'{dateStr2}')>=0 ) AS t
                                        GROUP BY  XFYId) AS C
                                        ON A.Gid = C.XFYId
                                WHERE (A.Name <> '' AND A.sys_IsDelete=0)
                                GROUP BY B.sys_Creater,B.sys_CreaterName";

                List<WorkStaticDto> list0 = _assessPersonRep.AsSugarClient().SqlQueryable<WorkStaticDto>(sql0).ToList();
                List<WorkStaticDto> list1 = _assessPersonRep.AsSugarClient().SqlQueryable<WorkStaticDto>(sql1).ToList();
                List<WorkStaticDto> list2 = _assessPersonRep.AsSugarClient().SqlQueryable<WorkStaticDto>(sql2).ToList();
                List<WorkStaticDto> list3 = _assessPersonRep.AsSugarClient().SqlQueryable<WorkStaticDto>(sql3).ToList();
                List<WorkStaticDto> list4 = _assessPersonRep.AsSugarClient().SqlQueryable<WorkStaticDto>(sql4).ToList();
                List<WorkStaticDto> list5 = _assessPersonRep.AsSugarClient().SqlQueryable<WorkStaticDto>(sql5).ToList();
                List<WorkStaticDto> list6 = _assessPersonRep.AsSugarClient().SqlQueryable<WorkStaticDto>(sql6).ToList();
                List<WorkStaticDto> list7 = _assessPersonRep.AsSugarClient().SqlQueryable<WorkStaticDto>(sql7).ToList();

                foreach (var item in list0)
                {
                    foreach (var item1 in list1)
                    {
                        if (item.EmployeeId.Equals(item1.EmployeeId))
                        {
                            item.AssessCount = item1.AssessCount;
                        }
                    }
                    foreach (var item2 in list2)
                    {
                        if (item.EmployeeId.Equals(item2.EmployeeId))
                        {
                            item.DBCount += item2.DBCount;
                        }
                    }
                    foreach (var item3 in list3)
                    {
                        if (item.EmployeeId.Equals(item3.EmployeeId))
                        {
                            item.DBCount += item3.DBCount;
                        }
                    }
                    foreach (var item4 in list4)
                    {
                        if (item.EmployeeId.Equals(item4.EmployeeId))
                        {
                            item.JZCount += item4.JZCount;
                        }
                    }
                    foreach (var item5 in list5)
                    {
                        if (item.EmployeeId.Equals(item5.EmployeeId))
                        {
                            item.JZCount += item5.JZCount;
                        }
                    }
                    foreach (var item6 in list6)
                    {
                        if (item.EmployeeId.Equals(item6.EmployeeId))
                        {
                            item.RijianCount = item6.RijianCount;
                        }
                    }
                    foreach (var item7 in list7)
                    {
                        if (item.EmployeeId.Equals(item7.EmployeeId))
                        {
                            item.XFYCount = item7.XFYCount;
                        }
                    }
                }
                return list0;
            }
            else
            {
                var sql0 = "select Gid as EmployeeId,EmployeeName,EmployeeAvator,Type from cts_Employee where Type=0";
                //身体能力评估 0.5 积分/人
                var sql1 = $@"SELECT A.sys_Creater as EmployeeId,
                                            A.sys_CreaterName as EmployeeName,
                                             cast(COUNT(0)*1./2 AS decimal(18,
                                            1)) AS AssessCount
                                    FROM cts_AssessInfo A
                                    INNER JOIN cts_AssessPerson B
                                        ON A.AssessPeresonId=B.Gid
                                    WHERE A.IsDelete=0
                                            AND B.IsDelete=0
                                            AND DATEDIFF(DAY,'{dateStr1}',A.sys_CreateDate)>=0
                                            AND DATEDIFF(DAY,A.sys_CreateDate,'{dateStr2}')>=0
                                    GROUP BY  A.sys_Creater,A.sys_CreaterName ";
                //低保评估 0.5 积分/人,核查信息
                var sql2 = $@"SELECT A.sys_Creater as EmployeeId,
                                            A.sys_CreaterName as EmployeeName,
                                                cast(COUNT(0)*1./2 AS decimal(18,
                                                1)) AS DBCount
                                        FROM cts_DBInfo A
                                        INNER JOIN cts_DBPerson B
                                            ON A.DBPersonId=B.Gid
                                        WHERE A.sys_IsDelete=0
                                                AND B.sys_IsDelete=0
                                                AND DATEDIFF(DAY,'{dateStr1}',A.sys_CreateDate)>=0
                                                AND DATEDIFF(DAY,A.sys_CreateDate,'{dateStr2}')>=0
                                        GROUP BY  A.sys_Creater,A.sys_CreaterName ";
                //低保评估 0.5 积分/人,上传照片
                var sql3 = $@"SELECT sys_Creater as EmployeeId,
                                            sys_CreaterName as EmployeeName,
                                         SUM(CAST(0.5 / PersoncCount AS decimal(18,
                                         2))) AS DBCount from
                                    (SELECT DISTINCT C.sys_Creater,
                                        C.sys_CreaterName,
                                        C.DBInfoId
                                    FROM cts_DBInfo A
                                    INNER JOIN cts_DBPerson B
                                        ON A.DBPersonId=B.Gid
                                    INNER JOIN cts_DBInfoImg C
                                        ON B.Gid=C.DBInfoId
                                    WHERE A.sys_IsDelete=0
                                            AND B.sys_IsDelete=0
                                            AND C.Type<7
                                            AND DATEDIFF(DAY,'{dateStr1}',A.sys_CreateDate)>=0
                                            AND DATEDIFF(DAY,A.sys_CreateDate,'{dateStr2}')>=0
                                            AND DATEDIFF(DAY,'{dateStr1}',C.sys_CreateDate)>=0
                                            AND DATEDIFF(DAY,C.sys_CreateDate,'{dateStr2}')>=0 )A
                                INNER JOIN 
                                    (SELECT DBInfoId,
                                        COUNT(0) AS PersoncCount FROM
                                        (SELECT DISTINCT C.sys_Creater,
                                        C.sys_CreaterName,
                                        C.DBInfoId
                                        FROM cts_DBInfo A
                                        INNER JOIN cts_DBPerson B
                                            ON A.DBPersonId=B.Gid
                                        INNER JOIN cts_DBInfoImg C
                                            ON B.Gid=C.DBInfoId
                                        WHERE A.sys_IsDelete=0
                                                AND B.sys_IsDelete=0
                                                AND C.Type<7
                                                AND DATEDIFF(DAY,'{dateStr1}',A.sys_CreateDate)>=0
                                                AND DATEDIFF(DAY,A.sys_CreateDate,'{dateStr2}')>=0
                                                AND DATEDIFF(DAY,'{dateStr1}',C.sys_CreateDate)>=0
                                                AND DATEDIFF(DAY,C.sys_CreateDate,'{dateStr2}')>=0 ) t
                                        GROUP BY  DBInfoId ) B
                                        ON A.DBInfoId=B.DBInfoId
                                GROUP BY  sys_Creater,sys_CreaterName";
                //救助评估 0.5 积分/人,核查信息
                var sql4 = $@"SELECT A.sys_Creater as EmployeeId,
                                            A.sys_CreaterName as EmployeeName,
                                        cast(COUNT(0)*1./2 AS decimal(18,
                                        1)) AS JZCount
                                FROM cts_JZInfo A
                                INNER JOIN cts_JZPerson B
                                    ON A.DBPersonId=B.Gid
                                WHERE A.sys_IsDelete=0
                                        AND B.sys_IsDelete=0
                                        AND DATEDIFF(DAY,'{dateStr1}',A.sys_CreateDate)>=0
                                        AND DATEDIFF(DAY,A.sys_CreateDate,'{dateStr2}')>=0
                                GROUP BY  A.sys_Creater,A.sys_CreaterName ";

                //救助评估 0.5 积分,上传图片
                var sql5 = $@"SELECT sys_Creater as EmployeeId,
                                            sys_CreaterName as EmployeeName,
                                         SUM(CAST(0.5 / PersoncCount AS decimal(18,
                                         2))) AS JZCount from
                                    (SELECT DISTINCT C.sys_Creater,
                                        C.sys_CreaterName,
                                        C.DBInfoId
                                    FROM cts_JZInfo A
                                    INNER JOIN cts_JZPerson B
                                        ON A.DBPersonId=B.Gid
                                    INNER JOIN cts_JZInfoImg C
                                        ON B.Gid=C.DBInfoId
                                    WHERE A.sys_IsDelete=0
                                            AND B.sys_IsDelete=0
                                            AND C.Type<7
                                            AND DATEDIFF(DAY,'{dateStr1}',A.sys_CreateDate)>=0
                                            AND DATEDIFF(DAY,A.sys_CreateDate,'{dateStr2}')>=0
                                            AND DATEDIFF(DAY,'{dateStr1}',C.sys_CreateDate)>=0
                                            AND DATEDIFF(DAY,C.sys_CreateDate,'{dateStr2}')>=0 )A
                                INNER JOIN 
                                    (SELECT DBInfoId,
                                        COUNT(0) AS PersoncCount FROM
                                        (SELECT DISTINCT C.sys_Creater,
                                        C.sys_CreaterName,
                                        C.DBInfoId
                                        FROM cts_JZInfo A
                                        INNER JOIN cts_JZPerson B
                                            ON A.DBPersonId=B.Gid
                                        INNER JOIN cts_JZInfoImg C
                                            ON B.Gid=C.DBInfoId
                                        WHERE A.sys_IsDelete=0
                                                AND B.sys_IsDelete=0
                                                AND C.Type<7
                                                AND DATEDIFF(DAY,'{dateStr1}',A.sys_CreateDate)>=0
                                                AND DATEDIFF(DAY,A.sys_CreateDate,'{dateStr2}')>=0
                                                AND DATEDIFF(DAY,'{dateStr1}',C.sys_CreateDate)>=0
                                                AND DATEDIFF(DAY,C.sys_CreateDate,'{dateStr2}')>=0 ) t
                                        GROUP BY  DBInfoId ) B
                                        ON A.DBInfoId=B.DBInfoId
                                GROUP BY  sys_Creater,sys_CreaterName";

                //日间照料中心 4 积分
                var sql6 = $@"SELECT 
                                            B.sys_Creater as EmployeeId,
                                            B.sys_CreaterName as EmployeeName,
		                                 SUM(
                                         CAST(4.0 / C.PersonCount AS decimal(18,
                                         2))) AS RijianCount
                                FROM dbo.cts_AssessRijian AS A
                                INNER JOIN 
                                    (SELECT DISTINCT OrgId,
                                         sys_Creater,
                                         sys_CreaterName
                                    FROM dbo.cts_RijianAssessInfo WHERE DATEDIFF(DAY,'{dateStr1}',sys_CreateDate)>=0 AND DATEDIFF(DAY,sys_CreateDate,'{dateStr2}')>=0) AS B
                                    ON A.Gid = B.OrgId
                                INNER JOIN 
                                    (SELECT OrgId,
                                         COUNT(0) AS PersonCount
                                    FROM 
                                        (SELECT DISTINCT OrgId,
                                         sys_Creater,
                                         sys_CreaterName
                                        FROM dbo.cts_RijianAssessInfo  WHERE DATEDIFF(DAY,'{dateStr1}',sys_CreateDate)>=0 AND DATEDIFF(DAY,sys_CreateDate,'{dateStr2}')>=0 ) AS t
                                        GROUP BY  OrgId) AS C
                                        ON A.Gid = C.OrgId
                                WHERE (A.Name <> '' AND A.State>0  AND DATEDIFF(DAY,'{dateStr1}',A.CheckTime)>=0 AND DATEDIFF(DAY,A.CheckTime,'{dateStr2}')>=0  AND A.sys_IsDelete=0)
                                GROUP BY B.sys_Creater,B.sys_CreaterName";

                //幸福院 2 积分
                var sql7 = $@" SELECT  
                                         B.sys_Creater as EmployeeId,
                                            B.sys_CreaterName as EmployeeName,
		                                 SUM(
                                         CAST(2.0 / C.PersonCount AS decimal(18,
                                         2))) AS XFYCount
                                FROM dbo.cts_AssessXFY AS A
                                INNER JOIN 
                                    (SELECT DISTINCT XFYId,
                                         sys_Creater,
                                         sys_CreaterName
                                    FROM dbo.cts_XFYAssessInfo WHERE DATEDIFF(DAY,'{dateStr1}',sys_CreateDate)>=0 AND DATEDIFF(DAY,sys_CreateDate,'{dateStr2}')>=0) AS B
                                    ON A.Gid = B.XFYId
                                INNER JOIN 
                                    (SELECT XFYId,
                                         COUNT(0) AS PersonCount
                                    FROM 
                                        (SELECT DISTINCT XFYId,
                                         sys_Creater,
                                         sys_CreaterName
                                        FROM dbo.cts_XFYAssessInfo  WHERE DATEDIFF(DAY,'{dateStr1}',sys_CreateDate)>=0 AND DATEDIFF(DAY,sys_CreateDate,'{dateStr2}')>=0 ) AS t
                                        GROUP BY  XFYId) AS C
                                        ON A.Gid = C.XFYId
                                WHERE (A.Name <> '' AND A.State>0  AND DATEDIFF(DAY,'{dateStr1}',A.CheckTime)>=0 AND DATEDIFF(DAY,A.CheckTime,'{dateStr2}')>=0  AND A.sys_IsDelete=0)
                                GROUP BY B.sys_Creater,B.sys_CreaterName";

                List<WorkStaticDto> list0 = _assessPersonRep.AsSugarClient().SqlQueryable<WorkStaticDto>(sql0).ToList();
                List<WorkStaticDto> list1 = _assessPersonRep.AsSugarClient().SqlQueryable<WorkStaticDto>(sql1).ToList();
                List<WorkStaticDto> list2 = _assessPersonRep.AsSugarClient().SqlQueryable<WorkStaticDto>(sql2).ToList();
                List<WorkStaticDto> list3 = _assessPersonRep.AsSugarClient().SqlQueryable<WorkStaticDto>(sql3).ToList();
                List<WorkStaticDto> list4 = _assessPersonRep.AsSugarClient().SqlQueryable<WorkStaticDto>(sql4).ToList();
                List<WorkStaticDto> list5 = _assessPersonRep.AsSugarClient().SqlQueryable<WorkStaticDto>(sql5).ToList();
                List<WorkStaticDto> list6 = _assessPersonRep.AsSugarClient().SqlQueryable<WorkStaticDto>(sql6).ToList();
                List<WorkStaticDto> list7 = _assessPersonRep.AsSugarClient().SqlQueryable<WorkStaticDto>(sql7).ToList();

                foreach (var item in list0)
                {
                    foreach (var item1 in list1)
                    {
                        if (item.EmployeeId.Equals(item1.EmployeeId))
                        {
                            item.AssessCount = item1.AssessCount;
                        }
                    }
                    foreach (var item2 in list2)
                    {
                        if (item.EmployeeId.Equals(item2.EmployeeId))
                        {
                            item.DBCount += item2.DBCount;
                        }
                    }
                    foreach (var item3 in list3)
                    {
                        if (item.EmployeeId.Equals(item3.EmployeeId))
                        {
                            item.DBCount += item3.DBCount;
                        }
                    }
                    foreach (var item4 in list4)
                    {
                        if (item.EmployeeId.Equals(item4.EmployeeId))
                        {
                            item.JZCount += item4.JZCount;
                        }
                    }
                    foreach (var item5 in list5)
                    {
                        if (item.EmployeeId.Equals(item5.EmployeeId))
                        {
                            item.JZCount += item5.JZCount;
                        }
                    }
                    foreach (var item6 in list6)
                    {
                        if (item.EmployeeId.Equals(item6.EmployeeId))
                        {
                            item.RijianCount = item6.RijianCount;
                        }
                    }
                    foreach (var item7 in list7)
                    {
                        if (item.EmployeeId.Equals(item7.EmployeeId))
                        {
                            item.XFYCount = item7.XFYCount;
                        }
                    }
                }
                list0 = list0.FindAll(e => e.AssessCount > 0 || e.DBCount > 0 || e.JZCount > 0 || e.RijianCount > 0 || e.XFYCount > 0);
                list0.Sort();
                return list0;
            }



        }

        /// <summary>
        /// 统计数据
        /// </summary>
        /// <param name="dateStr1"></param>
        /// <param name="dateStr2"></param>
        /// <returns></returns>
        public dynamic GetWorkCountbyDate(string dateStr1, string dateStr2) {

           
            if (string.IsNullOrEmpty(dateStr1))
            {
                dateStr1 = "2015-01-01";
            }
            if (string.IsNullOrEmpty(dateStr2))
            {
                dateStr2 = DateTime.Now.ToString("yyyy-MM-dd");
            }
            var input = new ReportDto
            {
                sdate = dateStr1,
                edate = dateStr2
            };
            if (UserHelper.GetAuth() != (UserHelper.GetAuth() | 2))
            {
                var sql0 = "select Gid as EmployeeId,EmployeeName,EmployeeAvator,Type from cts_Employee where Type=0";
                //身体能力评估 0.5 积分/人
                var sql1 = $@"SELECT A.sys_Creater as EmployeeId,
                                            A.sys_CreaterName as EmployeeName,
                                             cast(COUNT(0)*1./2 AS decimal(18,
                                            1)) AS AssessCount
                                    FROM cts_AssessInfo A
                                    INNER JOIN cts_AssessPerson B
                                        ON A.AssessPeresonId=B.Gid
                                    WHERE A.sys_IsDelete=0
                                            AND B.sys_IsDelete=0
                                            AND A.sys_Creater='{UserHelper.GetUserId()}' 
                                            AND DATEDIFF(DAY,'{input.sdate}',A.sys_CreateDate)>=0
                                            AND DATEDIFF(DAY,A.sys_CreateDate,'{input.edate}')>=0
                                    GROUP BY  A.sys_Creater,A.sys_CreaterName ";
                //低保评估 0.5 积分/人,核查信息
                var sql2 = $@"SELECT A.sys_Creater as EmployeeId,
                                            A.sys_CreaterName as EmployeeName,
                                                cast(COUNT(0)*1./2 AS decimal(18,
                                                1)) AS DBCount
                                        FROM cts_DBInfo A
                                        INNER JOIN cts_DBPerson B
                                            ON A.DBPersonId=B.Gid
                                        WHERE A.sys_IsDelete=0 
                                                AND B.sys_IsDelete=0
                                                AND A.sys_Creater='{UserHelper.GetUserId()}' 
                                                AND DATEDIFF(DAY,'{input.sdate}',A.sys_CreateDate)>=0
                                                AND DATEDIFF(DAY,A.sys_CreateDate,'{input.edate}')>=0
                                        GROUP BY  A.sys_Creater,A.sys_CreaterName ";
                //低保评估 0.5 积分/人,上传照片
                var sql3 = $@"SELECT sys_Creater as EmployeeId,
                                            sys_CreaterName as EmployeeName,
                                         SUM(CAST(0.5 / PersoncCount AS decimal(18,
                                         2))) AS DBCount from
                                    (SELECT DISTINCT C.sys_Creater,
                                        C.sys_CreaterName,
                                        C.DBInfoId
                                    FROM cts_DBInfo A
                                    INNER JOIN cts_DBPerson B
                                        ON A.DBPersonId=B.Gid
                                    INNER JOIN cts_DBInfoImg C
                                        ON B.Gid=C.DBInfoId
                                    WHERE A.sys_IsDelete=0
                                            AND B.sys_IsDelete=0
                                            AND C.sys_Creater='{UserHelper.GetUserId()}' 
                                            AND C.Type<7
                                            AND DATEDIFF(DAY,'{input.sdate}',A.sys_CreateDate)>=0
                                            AND DATEDIFF(DAY,A.sys_CreateDate,'{input.edate}')>=0
                                            AND DATEDIFF(DAY,'{input.sdate}',C.sys_CreateDate)>=0
                                            AND DATEDIFF(DAY,C.sys_CreateDate,'{input.edate}')>=0 )A
                                INNER JOIN 
                                    (SELECT DBInfoId,
                                        COUNT(0) AS PersoncCount FROM
                                        (SELECT DISTINCT C.sys_Creater,
                                        C.sys_CreaterName,
                                        C.DBInfoId
                                        FROM cts_DBInfo A
                                        INNER JOIN cts_DBPerson B
                                            ON A.DBPersonId=B.Gid
                                        INNER JOIN cts_DBInfoImg C
                                            ON B.Gid=C.DBInfoId
                                        WHERE A.sys_IsDelete=0
                                                AND B.sys_IsDelete=0 
                                                AND C.Type<7
                                                AND DATEDIFF(DAY,'{input.sdate}',A.sys_CreateDate)>=0
                                                AND DATEDIFF(DAY,A.sys_CreateDate,'{input.edate}')>=0
                                                AND DATEDIFF(DAY,'{input.sdate}',C.sys_CreateDate)>=0
                                                AND DATEDIFF(DAY,C.sys_CreateDate,'{input.edate}')>=0 ) t
                                        GROUP BY  DBInfoId ) B
                                        ON A.DBInfoId=B.DBInfoId
                                GROUP BY  sys_Creater,sys_CreaterName";
                //救助评估 0.5 积分/人,核查信息
                var sql4 = $@"SELECT A.sys_Creater as EmployeeId,
                                            A.sys_CreaterName as EmployeeName,
                                        cast(COUNT(0)*1./2 AS decimal(18,
                                        1)) AS JZCount
                                FROM cts_JZInfo A
                                INNER JOIN cts_JZPerson B
                                    ON A.DBPersonId=B.Gid
                                WHERE A.sys_IsDelete=0
                                        AND B.sys_IsDelete=0
                                        AND A.sys_Creater='{UserHelper.GetUserId()}' 
                                        AND DATEDIFF(DAY,'{input.sdate}',A.sys_CreateDate)>=0
                                        AND DATEDIFF(DAY,A.sys_CreateDate,'{input.edate}')>=0
                                GROUP BY  A.sys_Creater,A.sys_CreaterName ";

                //救助评估 0.5 积分,上传图片
                var sql5 = $@"SELECT sys_Creater as EmployeeId,
                                            sys_CreaterName as EmployeeName,
                                         SUM(CAST(0.5 / PersoncCount AS decimal(18,
                                         2))) AS JZCount from
                                    (SELECT DISTINCT C.sys_Creater,
                                        C.sys_CreaterName,
                                        C.DBInfoId
                                    FROM cts_JZInfo A
                                    INNER JOIN cts_JZPerson B
                                        ON A.DBPersonId=B.Gid
                                    INNER JOIN cts_JZInfoImg C
                                        ON B.Gid=C.DBInfoId
                                    WHERE A.sys_IsDelete=0
                                            AND B.sys_IsDelete=0
                                            AND C.sys_Creater='{UserHelper.GetUserId()}' 
                                            AND C.Type<7
                                            AND DATEDIFF(DAY,'{input.sdate}',A.sys_CreateDate)>=0
                                            AND DATEDIFF(DAY,A.sys_CreateDate,'{input.edate}')>=0
                                            AND DATEDIFF(DAY,'{input.sdate}',C.sys_CreateDate)>=0
                                            AND DATEDIFF(DAY,C.sys_CreateDate,'{input.edate}')>=0 )A
                                INNER JOIN 
                                    (SELECT DBInfoId,
                                        COUNT(0) AS PersoncCount FROM
                                        (SELECT DISTINCT C.sys_Creater,
                                        C.sys_CreaterName,
                                        C.DBInfoId
                                        FROM cts_JZInfo A
                                        INNER JOIN cts_JZPerson B
                                            ON A.DBPersonId=B.Gid
                                        INNER JOIN cts_JZInfoImg C
                                            ON B.Gid=C.DBInfoId
                                        WHERE A.sys_IsDelete=0
                                                AND B.sys_IsDelete=0 
                                                AND C.Type<7
                                                AND DATEDIFF(DAY,'{input.sdate}',A.sys_CreateDate)>=0
                                                AND DATEDIFF(DAY,A.sys_CreateDate,'{input.edate}')>=0
                                                AND DATEDIFF(DAY,'{input.sdate}',C.sys_CreateDate)>=0
                                                AND DATEDIFF(DAY,C.sys_CreateDate,'{input.edate}')>=0 ) t
                                        GROUP BY  DBInfoId ) B
                                        ON A.DBInfoId=B.DBInfoId
                                GROUP BY  sys_Creater,sys_CreaterName";

                //日间照料中心 4 积分
                var sql6 = $@"SELECT 
                                            B.sys_Creater as EmployeeId,
                                            B.sys_CreaterName as EmployeeName,
		                                 SUM(
                                         CAST(4.0 / C.PersonCount AS decimal(18,
                                         2))) AS RijianCount
                                FROM dbo.cts_AssessRijian AS A
                                INNER JOIN 
                                    (SELECT DISTINCT OrgId,
                                         sys_Creater,
                                         sys_CreaterName
                                    FROM dbo.cts_RijianAssessInfo WHERE sys_Creater='{UserHelper.GetUserId()}' AND   DATEDIFF(DAY,'{input.sdate}',sys_CreateDate)>=0 AND DATEDIFF(DAY,sys_CreateDate,'{input.edate}')>=0) AS B
                                    ON A.Gid = B.OrgId
                                INNER JOIN 
                                    (SELECT OrgId,
                                         COUNT(0) AS PersonCount
                                    FROM 
                                        (SELECT DISTINCT OrgId,
                                         sys_Creater,
                                         sys_CreaterName
                                        FROM dbo.cts_RijianAssessInfo  WHERE   DATEDIFF(DAY,'{input.sdate}',sys_CreateDate)>=0 AND DATEDIFF(DAY,sys_CreateDate,'{input.edate}')>=0 ) AS t
                                        GROUP BY  OrgId) AS C
                                        ON A.Gid = C.OrgId
                                WHERE (A.Name <> '' AND A.sys_IsDelete=0)
                                GROUP BY B.sys_Creater,B.sys_CreaterName";

                //幸福院 2 积分
                var sql7 = $@" SELECT  
                                         B.sys_Creater as EmployeeId,
                                            B.sys_CreaterName as EmployeeName,
		                                 SUM(
                                         CAST(2.0 / C.PersonCount AS decimal(18,
                                         2))) AS XFYCount
                                FROM dbo.cts_AssessXFY AS A
                                INNER JOIN 
                                    (SELECT DISTINCT XFYId,
                                         sys_Creater,
                                         sys_CreaterName
                                    FROM dbo.cts_XFYAssessInfo WHERE sys_Creater='{UserHelper.GetUserId()}'  AND DATEDIFF(DAY,'{input.sdate}',sys_CreateDate)>=0 AND DATEDIFF(DAY,sys_CreateDate,'{input.edate}')>=0) AS B
                                    ON A.Gid = B.XFYId
                                INNER JOIN 
                                    (SELECT XFYId,
                                         COUNT(0) AS PersonCount
                                    FROM 
                                        (SELECT DISTINCT XFYId,
                                         sys_Creater,
                                         sys_CreaterName
                                        FROM dbo.cts_XFYAssessInfo  WHERE  DATEDIFF(DAY,'{input.sdate}',sys_CreateDate)>=0 AND DATEDIFF(DAY,sys_CreateDate,'{input.edate}')>=0 ) AS t
                                        GROUP BY  XFYId) AS C
                                        ON A.Gid = C.XFYId
                                WHERE (A.Name <> '' AND A.sys_IsDelete=0)
                                GROUP BY B.sys_Creater,B.sys_CreaterName";

                var list0 = _assessPersonRep.AsSugarClient().SqlQueryable<WorkStaticDto>(sql0).ToList();
                var list1 = _assessPersonRep.AsSugarClient().SqlQueryable<WorkStaticDto>(sql1).ToList();
                var list2 = _assessPersonRep.AsSugarClient().SqlQueryable<WorkStaticDto>(sql2).ToList();
                var list3 = _assessPersonRep.AsSugarClient().SqlQueryable<WorkStaticDto>(sql3).ToList();
                var list4 = _assessPersonRep.AsSugarClient().SqlQueryable<WorkStaticDto>(sql4).ToList();
                var list5 = _assessPersonRep.AsSugarClient().SqlQueryable<WorkStaticDto>(sql5).ToList();
                var list6 = _assessPersonRep.AsSugarClient().SqlQueryable<WorkStaticDto>(sql6).ToList();
                var list7 = _assessPersonRep.AsSugarClient().SqlQueryable<WorkStaticDto>(sql7).ToList();

                foreach (var item in list0)
                {
                    foreach (var item1 in list1)
                    {
                        if (item.EmployeeId.Equals(item1.EmployeeId))
                        {
                            item.AssessCount = item1.AssessCount;
                        }
                    }
                    foreach (var item2 in list2)
                    {
                        if (item.EmployeeId.Equals(item2.EmployeeId))
                        {
                            item.DBCount += item2.DBCount;
                        }
                    }
                    foreach (var item3 in list3)
                    {
                        if (item.EmployeeId.Equals(item3.EmployeeId))
                        {
                            item.DBCount += item3.DBCount;
                        }
                    }
                    foreach (var item4 in list4)
                    {
                        if (item.EmployeeId.Equals(item4.EmployeeId))
                        {
                            item.JZCount += item4.JZCount;
                        }
                    }
                    foreach (var item5 in list5)
                    {
                        if (item.EmployeeId.Equals(item5.EmployeeId))
                        {
                            item.JZCount += item5.JZCount;
                        }
                    }
                    foreach (var item6 in list6)
                    {
                        if (item.EmployeeId.Equals(item6.EmployeeId))
                        {
                            item.RijianCount = item6.RijianCount;
                        }
                    }
                    foreach (var item7 in list7)
                    {
                        if (item.EmployeeId.Equals(item7.EmployeeId))
                        {
                            item.XFYCount = item7.XFYCount;
                        }
                    }
                }
                return list0;
            }
            else {
                var sql0 = "select Gid as EmployeeId,EmployeeName,EmployeeAvator,Type from cts_Employee where Type=0";
                //身体能力评估 0.5 积分/人
                var sql1 = $@"SELECT A.sys_Creater as EmployeeId,
                                            A.sys_CreaterName as EmployeeName,
                                             cast(COUNT(0)*1./2 AS decimal(18,
                                            1)) AS AssessCount
                                    FROM cts_AssessInfo A
                                    INNER JOIN cts_AssessPerson B
                                        ON A.AssessPeresonId=B.Gid
                                    WHERE A.sys_IsDelete=0
                                            AND B.sys_IsDelete=0
                                            AND DATEDIFF(DAY,'{input.sdate}',A.sys_CreateDate)>=0
                                            AND DATEDIFF(DAY,A.sys_CreateDate,'{input.edate}')>=0
                                    GROUP BY  A.sys_Creater,A.sys_CreaterName ";
                //低保评估 0.5 积分/人,核查信息
                var sql2 = $@"SELECT A.sys_Creater as EmployeeId,
                                            A.sys_CreaterName as EmployeeName,
                                                cast(COUNT(0)*1./2 AS decimal(18,
                                                1)) AS DBCount
                                        FROM cts_DBInfo A
                                        INNER JOIN cts_DBPerson B
                                            ON A.DBPersonId=B.Gid
                                        WHERE A.sys_IsDelete=0
                                                AND B.sys_IsDelete=0
                                                AND DATEDIFF(DAY,'{input.sdate}',A.sys_CreateDate)>=0
                                                AND DATEDIFF(DAY,A.sys_CreateDate,'{input.edate}')>=0
                                        GROUP BY  A.sys_Creater,A.sys_CreaterName ";
                //低保评估 0.5 积分/人,上传照片
                var sql3 = $@"SELECT sys_Creater as EmployeeId,
                                            sys_CreaterName as EmployeeName,
                                         SUM(CAST(0.5 / PersoncCount AS decimal(18,
                                         2))) AS DBCount from
                                    (SELECT DISTINCT C.sys_Creater,
                                        C.sys_CreaterName,
                                        C.DBInfoId
                                    FROM cts_DBInfo A
                                    INNER JOIN cts_DBPerson B
                                        ON A.DBPersonId=B.Gid
                                    INNER JOIN cts_DBInfoImg C
                                        ON B.Gid=C.DBInfoId
                                    WHERE A.sys_IsDelete=0
                                            AND B.sys_IsDelete=0
                                            AND C.Type<7
                                            AND DATEDIFF(DAY,'{input.sdate}',A.sys_CreateDate)>=0
                                            AND DATEDIFF(DAY,A.sys_CreateDate,'{input.edate}')>=0
                                            AND DATEDIFF(DAY,'{input.sdate}',C.sys_CreateDate)>=0
                                            AND DATEDIFF(DAY,C.sys_CreateDate,'{input.edate}')>=0 )A
                                INNER JOIN 
                                    (SELECT DBInfoId,
                                        COUNT(0) AS PersoncCount FROM
                                        (SELECT DISTINCT C.sys_Creater,
                                        C.sys_CreaterName,
                                        C.DBInfoId
                                        FROM cts_DBInfo A
                                        INNER JOIN cts_DBPerson B
                                            ON A.DBPersonId=B.Gid
                                        INNER JOIN cts_DBInfoImg C
                                            ON B.Gid=C.DBInfoId
                                        WHERE A.sys_IsDelete=0
                                                AND B.sys_IsDelete=0
                                                AND C.Type<7
                                                AND DATEDIFF(DAY,'{input.sdate}',A.sys_CreateDate)>=0
                                                AND DATEDIFF(DAY,A.sys_CreateDate,'{input.edate}')>=0
                                                AND DATEDIFF(DAY,'{input.sdate}',C.sys_CreateDate)>=0
                                                AND DATEDIFF(DAY,C.sys_CreateDate,'{input.edate}')>=0 ) t
                                        GROUP BY  DBInfoId ) B
                                        ON A.DBInfoId=B.DBInfoId
                                GROUP BY  sys_Creater,sys_CreaterName";
                //救助评估 0.5 积分/人,核查信息
                var sql4 = $@"SELECT A.sys_Creater as EmployeeId,
                                            A.sys_CreaterName as EmployeeName,
                                        cast(COUNT(0)*1./2 AS decimal(18,
                                        1)) AS JZCount
                                FROM cts_JZInfo A
                                INNER JOIN cts_JZPerson B
                                    ON A.DBPersonId=B.Gid
                                WHERE A.sys_IsDelete=0
                                        AND B.sys_IsDelete=0
                                        AND DATEDIFF(DAY,'{input.sdate}',A.sys_CreateDate)>=0
                                        AND DATEDIFF(DAY,A.sys_CreateDate,'{input.edate}')>=0
                                GROUP BY  A.sys_Creater,A.sys_CreaterName ";

                //救助评估 0.5 积分,上传图片
                var sql5 = $@"SELECT sys_Creater as EmployeeId,
                                            sys_CreaterName as EmployeeName,
                                         SUM(CAST(0.5 / PersoncCount AS decimal(18,
                                         2))) AS JZCount from
                                    (SELECT DISTINCT C.sys_Creater,
                                        C.sys_CreaterName,
                                        C.DBInfoId
                                    FROM cts_JZInfo A
                                    INNER JOIN cts_JZPerson B
                                        ON A.DBPersonId=B.Gid
                                    INNER JOIN cts_JZInfoImg C
                                        ON B.Gid=C.DBInfoId
                                    WHERE A.sys_IsDelete=0
                                            AND B.sys_IsDelete=0
                                            AND C.Type<7
                                            AND DATEDIFF(DAY,'{input.sdate}',A.sys_CreateDate)>=0
                                            AND DATEDIFF(DAY,A.sys_CreateDate,'{input.edate}')>=0
                                            AND DATEDIFF(DAY,'{input.sdate}',C.sys_CreateDate)>=0
                                            AND DATEDIFF(DAY,C.sys_CreateDate,'{input.edate}')>=0 )A
                                INNER JOIN 
                                    (SELECT DBInfoId,
                                        COUNT(0) AS PersoncCount FROM
                                        (SELECT DISTINCT C.sys_Creater,
                                        C.sys_CreaterName,
                                        C.DBInfoId
                                        FROM cts_JZInfo A
                                        INNER JOIN cts_JZPerson B
                                            ON A.DBPersonId=B.Gid
                                        INNER JOIN cts_JZInfoImg C
                                            ON B.Gid=C.DBInfoId
                                        WHERE A.sys_IsDelete=0
                                                AND B.sys_IsDelete=0
                                                AND C.Type<7
                                                AND DATEDIFF(DAY,'{input.sdate}',A.sys_CreateDate)>=0
                                                AND DATEDIFF(DAY,A.sys_CreateDate,'{input.edate}')>=0
                                                AND DATEDIFF(DAY,'{input.sdate}',C.sys_CreateDate)>=0
                                                AND DATEDIFF(DAY,C.sys_CreateDate,'{input.edate}')>=0 ) t
                                        GROUP BY  DBInfoId ) B
                                        ON A.DBInfoId=B.DBInfoId
                                GROUP BY  sys_Creater,sys_CreaterName";

                //日间照料中心 4 积分
                var sql6 = $@"SELECT 
                                            B.sys_Creater as EmployeeId,
                                            B.sys_CreaterName as EmployeeName,
		                                 SUM(
                                         CAST(4.0 / C.PersonCount AS decimal(18,
                                         2))) AS RijianCount
                                FROM dbo.cts_AssessRijian AS A
                                INNER JOIN 
                                    (SELECT DISTINCT OrgId,
                                         sys_Creater,
                                         sys_CreaterName
                                    FROM dbo.cts_RijianAssessInfo WHERE DATEDIFF(DAY,'{input.sdate}',sys_CreateDate)>=0 AND DATEDIFF(DAY,sys_CreateDate,'{input.edate}')>=0) AS B
                                    ON A.Gid = B.OrgId
                                INNER JOIN 
                                    (SELECT OrgId,
                                         COUNT(0) AS PersonCount
                                    FROM 
                                        (SELECT DISTINCT OrgId,
                                         sys_Creater,
                                         sys_CreaterName
                                        FROM dbo.cts_RijianAssessInfo  WHERE DATEDIFF(DAY,'{input.sdate}',sys_CreateDate)>=0 AND DATEDIFF(DAY,sys_CreateDate,'{input.edate}')>=0 ) AS t
                                        GROUP BY  OrgId) AS C
                                        ON A.Gid = C.OrgId
                                WHERE (A.Name <> '' AND A.State>0  AND DATEDIFF(DAY,'{input.sdate}',A.CheckTime)>=0 AND DATEDIFF(DAY,A.CheckTime,'{input.edate}')>=0  AND A.sys_IsDelete=0)
                                GROUP BY B.sys_Creater,B.sys_CreaterName";

                //幸福院 2 积分
                var sql7 = $@" SELECT  
                                         B.sys_Creater as EmployeeId,
                                            B.sys_CreaterName as EmployeeName,
		                                 SUM(
                                         CAST(2.0 / C.PersonCount AS decimal(18,
                                         2))) AS XFYCount
                                FROM dbo.cts_AssessXFY AS A
                                INNER JOIN 
                                    (SELECT DISTINCT XFYId,
                                         sys_Creater,
                                         sys_CreaterName
                                    FROM dbo.cts_XFYAssessInfo WHERE DATEDIFF(DAY,'{input.sdate}',sys_CreateDate)>=0 AND DATEDIFF(DAY,sys_CreateDate,'{input.edate}')>=0) AS B
                                    ON A.Gid = B.XFYId
                                INNER JOIN 
                                    (SELECT XFYId,
                                         COUNT(0) AS PersonCount
                                    FROM 
                                        (SELECT DISTINCT XFYId,
                                         sys_Creater,
                                         sys_CreaterName
                                        FROM dbo.cts_XFYAssessInfo  WHERE DATEDIFF(DAY,'{input.sdate}',sys_CreateDate)>=0 AND DATEDIFF(DAY,sys_CreateDate,'{input.edate}')>=0 ) AS t
                                        GROUP BY  XFYId) AS C
                                        ON A.Gid = C.XFYId
                                WHERE (A.Name <> '' AND A.State>0  AND DATEDIFF(DAY,'{input.sdate}',A.CheckTime)>=0 AND DATEDIFF(DAY,A.CheckTime,'{input.edate}')>=0  AND A.sys_IsDelete=0)
                                GROUP BY B.sys_Creater,B.sys_CreaterName"; 

                var list0 = _assessPersonRep.AsSugarClient().SqlQueryable<WorkStaticDto>(sql0).ToList();
                var list1 = _assessPersonRep.AsSugarClient().SqlQueryable<WorkStaticDto>(sql1).ToList();
                var list2 = _assessPersonRep.AsSugarClient().SqlQueryable<WorkStaticDto>(sql2).ToList();
                var list3 = _assessPersonRep.AsSugarClient().SqlQueryable<WorkStaticDto>(sql3).ToList();
                var list4 = _assessPersonRep.AsSugarClient().SqlQueryable<WorkStaticDto>(sql4).ToList();
                var list5 = _assessPersonRep.AsSugarClient().SqlQueryable<WorkStaticDto>(sql5).ToList();
                var list6 = _assessPersonRep.AsSugarClient().SqlQueryable<WorkStaticDto>(sql6).ToList();
                var list7 = _assessPersonRep.AsSugarClient().SqlQueryable<WorkStaticDto>(sql7).ToList();



                foreach (var item in list0)
                {
                    foreach (var item1 in list1)
                    {
                        if (item.EmployeeId.Equals(item1.EmployeeId))
                        {
                            item.AssessCount = item1.AssessCount;
                        }
                    }
                    foreach (var item2 in list2)
                    {
                        if (item.EmployeeId.Equals(item2.EmployeeId))
                        {
                            item.DBCount += item2.DBCount;
                        }
                    }
                    foreach (var item3 in list3)
                    {
                        if (item.EmployeeId.Equals(item3.EmployeeId))
                        {
                            item.DBCount += item3.DBCount;
                        }
                    }
                    foreach (var item4 in list4)
                    {
                        if (item.EmployeeId.Equals(item4.EmployeeId))
                        {
                            item.JZCount += item4.JZCount;
                        }
                    }
                    foreach (var item5 in list5)
                    {
                        if (item.EmployeeId.Equals(item5.EmployeeId))
                        {
                            item.JZCount += item5.JZCount;
                        }
                    }
                    foreach (var item6 in list6)
                    {
                        if (item.EmployeeId.Equals(item6.EmployeeId))
                        {
                            item.RijianCount = item6.RijianCount;
                        }
                    }
                    foreach (var item7 in list7)
                    {
                        if (item.EmployeeId.Equals(item7.EmployeeId))
                        {
                            item.XFYCount = item7.XFYCount;
                        }
                    }
                }
                list0 = list0.FindAll(e => e.AssessCount > 0 || e.DBCount > 0 || e.JZCount > 0 || e.RijianCount > 0 || e.XFYCount > 0);
                list0.Sort();
                return list0;

            }
            
        }


        //查询统计工作量详情
        public SqlSugarPagedXList<WorkStaticDetailDto> SearchWorkDetail(ReportDto input)
        {
            if (string.IsNullOrEmpty(input.sdate))
            {
                input.sdate = "2015-01-01";
            }
            if (string.IsNullOrEmpty(input.edate))
            {
                input.edate = DateTime.Now.ToString("yyyy-MM-dd");
            }
            var sql = $@"SELECT A.sys_Creater,A.sys_CreaterName,CONVERT(varchar(100),A.sys_CreateDate,20) AS sys_CreateDate,B.Gid,B.Name,C.GroupName FROM  cts_AssessInfo A 
                                INNER JOIN cts_AssessPerson B ON A.AssessPeresonId=B.Gid
                                INNER JOIN cts_PersonGroup C ON B.GroupId=C.Gid
                                WHERE B.sys_IsDelete=0 AND A.sys_Creater='{input.empid}' AND DATEDIFF(DAY,'{input.sdate}',A.sys_CreateDate)>=0 AND DATEDIFF(DAY,A.sys_CreateDate,'{input.edate}')>=0";
            sql += " ORDER BY A.sys_CreateDate DESC";
            return _assessPersonRep.AsSugarClient().SqlQueryable<WorkStaticDetailDto>(sql).ToPagedList(input.page, input.size);
             
        }
        //查询统计工作量详情
        public List<WorkStaticDetail2Dto> SearchWorkDetailByGroup(ReportDto input)
        {
            if (string.IsNullOrEmpty(input.sdate))
            {
                input.sdate = "2015-01-01";
            }
            if (string.IsNullOrEmpty(input.edate))
            {
                input.edate = DateTime.Now.ToString("yyyy-MM-dd");
            }

            var sqlstr = $@"SELECT B.GroupName,COUNT(0) AS AssessCount FROM  cts_AssessInfo A INNER JOIN cts_AssessPerson B ON A.AssessPeresonId=B.Gid
                                WHERE B.sys_IsDelete=0 AND A.sys_Creater='{input.empid}' AND DATEDIFF(DAY,'{input.sdate}',A.sys_CreateDate)>=0 AND DATEDIFF(DAY,A.sys_CreateDate,'{input.edate}')>=0
                                GROUP BY B.GroupName";
             
            return _assessPersonRep.AsSugarClient().SqlQueryable<WorkStaticDetail2Dto>(sqlstr).ToList();
            
        }

        //查询我的工作量
        public dynamic GetMyWork()
        {

            var userId = UserHelper.GetUserId();
            //身体能力评估 0.5 积分/人_当月
            var sql1 = $@"SELECT  cast(COUNT(0)*1./2 AS decimal(18, 1)) AS AssessCount
                                    FROM cts_AssessInfo A
                                    INNER JOIN cts_AssessPerson B
                                        ON A.AssessPeresonId=B.Gid
                                    WHERE A.sys_IsDelete=0
                                            AND B.sys_IsDelete=0
                                            AND A.sys_Creater='{userId}' 
                                            AND A.sys_CreateDate>= dateadd(month, datediff(month, 0, getdate()), 0)  ";
            //低保评估 0.5 积分/人,核查信息_当月
            var sql2 = $@"SELECT   cast(COUNT(0)*1./2 AS decimal(18, 1)) AS DBCount
                                        FROM cts_DBInfo A
                                        INNER JOIN cts_DBPerson B
                                            ON A.DBPersonId=B.Gid
                                        WHERE A.sys_IsDelete=0 
                                                AND B.sys_IsDelete=0
                                                AND A.sys_Creater='{userId}' 
                                                AND A.sys_CreateDate>= dateadd(month, datediff(month, 0, getdate()), 0)";
            //低保评估 0.5 积分/人,上传照片_当月
            var sql3 = $@"SELECT  ISNULL( SUM(CAST(0.5 / PersoncCount AS decimal(18, 2))),0) AS DBCount from
                                    (SELECT DISTINCT C.sys_Creater,
                                        C.sys_CreaterName,
                                        C.DBInfoId
                                    FROM cts_DBInfo A
                                    INNER JOIN cts_DBPerson B
                                        ON A.DBPersonId=B.Gid
                                    INNER JOIN cts_DBInfoImg C
                                        ON B.Gid=C.DBInfoId
                                    WHERE A.sys_IsDelete=0
                                            AND B.sys_IsDelete=0
                                            AND C.sys_Creater='{userId}' 
                                            AND C.Type<7
                                            AND A.sys_CreateDate>= dateadd(month, datediff(month, 0, getdate()), 0)
                                            AND C.sys_CreateDate>= dateadd(month, datediff(month, 0, getdate()), 0)) A
                                INNER JOIN 
                                    (SELECT DBInfoId,
                                        COUNT(0) AS PersoncCount FROM
                                        (SELECT DISTINCT C.sys_Creater,
                                        C.sys_CreaterName,
                                        C.DBInfoId
                                        FROM cts_DBInfo A
                                        INNER JOIN cts_DBPerson B
                                            ON A.DBPersonId=B.Gid
                                        INNER JOIN cts_DBInfoImg C
                                            ON B.Gid=C.DBInfoId
                                        WHERE A.sys_IsDelete=0
                                                AND B.sys_IsDelete=0                                               
                                                AND C.Type<7
                                                AND A.sys_CreateDate>= dateadd(month, datediff(month, 0, getdate()), 0)
                                            AND C.sys_CreateDate>= dateadd(month, datediff(month, 0, getdate()), 0) ) t
                                        GROUP BY  DBInfoId ) B
                                        ON A.DBInfoId=B.DBInfoId
                              ";
            //救助评估 0.5 积分/人,核查信息_当月
            var sql4 = $@"SELECT  cast(COUNT(0)*1./2 AS decimal(18, 1)) AS JZCount
                                        FROM cts_JZInfo A
                                        INNER JOIN cts_JZPerson B
                                            ON A.DBPersonId=B.Gid
                                        WHERE A.sys_IsDelete=0 
                                                AND B.sys_IsDelete=0
                                                AND A.sys_Creater='{userId}' 
                                                AND A.sys_CreateDate>= dateadd(month, datediff(month, 0, getdate()), 0)";

            //救助评估 0.5 积分,上传图片_当月
            var sql5 = $@"SELECT  ISNULL( SUM(CAST(0.5 / PersoncCount AS decimal(18, 2))),0) AS JZCount from
                                    (SELECT DISTINCT C.sys_Creater,
                                        C.sys_CreaterName,
                                        C.DBInfoId
                                    FROM cts_JZInfo A
                                    INNER JOIN cts_JZPerson B
                                        ON A.DBPersonId=B.Gid
                                    INNER JOIN cts_JZInfoImg C
                                        ON B.Gid=C.DBInfoId
                                    WHERE A.sys_IsDelete=0
                                            AND B.sys_IsDelete=0
                                            AND C.sys_Creater='{userId}' 
                                            AND C.Type<7
                                            AND A.sys_CreateDate>= dateadd(month, datediff(month, 0, getdate()), 0)
                                            AND C.sys_CreateDate>= dateadd(month, datediff(month, 0, getdate()), 0)) A
                                INNER JOIN 
                                    (SELECT DBInfoId,
                                        COUNT(0) AS PersoncCount FROM
                                        (SELECT DISTINCT C.sys_Creater,
                                        C.sys_CreaterName,
                                        C.DBInfoId
                                        FROM cts_JZInfo A
                                        INNER JOIN cts_JZPerson B
                                            ON A.DBPersonId=B.Gid
                                        INNER JOIN cts_JZInfoImg C
                                            ON B.Gid=C.DBInfoId
                                        WHERE A.sys_IsDelete=0
                                                AND B.sys_IsDelete=0                                               
                                                AND C.Type<7
                                                AND A.sys_CreateDate>= dateadd(month, datediff(month, 0, getdate()), 0)
                                            AND C.sys_CreateDate>= dateadd(month, datediff(month, 0, getdate()), 0) ) t
                                        GROUP BY  DBInfoId ) B
                                        ON A.DBInfoId=B.DBInfoId";

            //日间照料中心 4 积分
            var sql6 = $@"SELECT ISNULL(SUM(CAST(4.0 / C.PersonCount AS decimal(18,2))),0) AS RijianCount
                                FROM dbo.cts_AssessRijian AS A
                                INNER JOIN 
                                    (SELECT DISTINCT OrgId,
                                         sys_Creater,
                                         sys_CreaterName
                                    FROM dbo.cts_RijianAssessInfo WHERE sys_Creater='{userId}' AND sys_CreateDate>= dateadd(month, datediff(month, 0, getdate()), 0)) AS B
                                    ON A.Gid = B.OrgId
                                INNER JOIN 
                                    (SELECT OrgId,
                                         COUNT(0) AS PersonCount
                                    FROM 
                                        (SELECT DISTINCT OrgId,
                                         sys_Creater,
                                         sys_CreaterName
                                        FROM dbo.cts_RijianAssessInfo  WHERE  sys_CreateDate>= dateadd(month, datediff(month, 0, getdate()), 0) ) AS t
                                        GROUP BY  OrgId) AS C
                                        ON A.Gid = C.OrgId
                                WHERE (A.Name <> '' AND A.sys_IsDelete=0)";

            //幸福院 2 积分 _当月
            var sql7 = $@" SELECT  ISNULL( SUM( CAST(2.0 / C.PersonCount AS decimal(18, 2))),0) AS XFYCount
                                FROM dbo.cts_AssessXFY AS A
                                INNER JOIN 
                                    (SELECT DISTINCT XFYId,
                                         sys_Creater,
                                         sys_CreaterName
                                    FROM dbo.cts_XFYAssessInfo WHERE sys_Creater='{userId}'  AND sys_CreateDate>= dateadd(month, datediff(month, 0, getdate()), 0) ) AS B
                                    ON A.Gid = B.XFYId
                                INNER JOIN 
                                    (SELECT XFYId,
                                         COUNT(0) AS PersonCount
                                    FROM 
                                        (SELECT DISTINCT XFYId,
                                         sys_Creater,
                                         sys_CreaterName
                                        FROM dbo.cts_XFYAssessInfo  WHERE  sys_CreateDate>= dateadd(month, datediff(month, 0, getdate()), 0) ) AS t
                                        GROUP BY  XFYId) AS C
                                        ON A.Gid = C.XFYId
                                WHERE (A.Name <> '' AND A.sys_IsDelete=0)
 ";

            // 身体能力评估 0.5 积分 / 人 
            var sql1_all = $@"SELECT  cast(COUNT(0)*1./2 AS decimal(18, 1)) AS AssessCount
                                    FROM cts_AssessInfo A
                                    INNER JOIN cts_AssessPerson B
                                        ON A.AssessPeresonId=B.Gid
                                    WHERE A.sys_IsDelete=0
                                            AND B.sys_IsDelete=0
                                            AND A.sys_Creater='{userId}' 
                                             ";
            //低保评估 0.5 积分/人,核查信息 
            var sql2_all = $@"SELECT   cast(COUNT(0)*1./2 AS decimal(18, 1)) AS DBCount
                                        FROM cts_DBInfo A
                                        INNER JOIN cts_DBPerson B
                                            ON A.DBPersonId=B.Gid
                                        WHERE A.sys_IsDelete=0 
                                                AND B.sys_IsDelete=0
                                                AND A.sys_Creater='{userId}'  ";
            //低保评估 0.5 积分/人,上传照片_当月
            var sql3_all = $@"SELECT  ISNULL( SUM(CAST(0.5 / PersoncCount AS decimal(18, 2))),0) AS DBCount from
                                    (SELECT DISTINCT C.sys_Creater,
                                        C.sys_CreaterName,
                                        C.DBInfoId
                                    FROM cts_DBInfo A
                                    INNER JOIN cts_DBPerson B
                                        ON A.DBPersonId=B.Gid
                                    INNER JOIN cts_DBInfoImg C
                                        ON B.Gid=C.DBInfoId
                                    WHERE A.sys_IsDelete=0
                                            AND B.sys_IsDelete=0
                                            AND C.sys_Creater='{userId}' 
                                            AND C.Type<7 ) A
                                INNER JOIN 
                                    (SELECT DBInfoId,
                                        COUNT(0) AS PersoncCount FROM
                                        (SELECT DISTINCT C.sys_Creater,
                                        C.sys_CreaterName,
                                        C.DBInfoId
                                        FROM cts_DBInfo A
                                        INNER JOIN cts_DBPerson B
                                            ON A.DBPersonId=B.Gid
                                        INNER JOIN cts_DBInfoImg C
                                            ON B.Gid=C.DBInfoId
                                        WHERE A.sys_IsDelete=0
                                                AND B.sys_IsDelete=0                                               
                                                AND C.Type<7   ) t
                                        GROUP BY  DBInfoId ) B
                                        ON A.DBInfoId=B.DBInfoId
                              ";
            //救助评估 0.5 积分/人,核查信息_当月
            var sql4_all = $@"SELECT  cast(COUNT(0)*1./2 AS decimal(18, 1)) AS JZCount
                                        FROM cts_JZInfo A
                                        INNER JOIN cts_JZPerson B
                                            ON A.DBPersonId=B.Gid
                                        WHERE A.sys_IsDelete=0 
                                                AND B.sys_IsDelete=0
                                                AND A.sys_Creater='{userId}'  ";

            //救助评估 0.5 积分,上传图片_当月
            var sql5_all = $@"SELECT  ISNULL( SUM(CAST(0.5 / PersoncCount AS decimal(18, 2))),0) AS JZCount from
                                    (SELECT DISTINCT C.sys_Creater,
                                        C.sys_CreaterName,
                                        C.DBInfoId
                                    FROM cts_JZInfo A
                                    INNER JOIN cts_JZPerson B
                                        ON A.DBPersonId=B.Gid
                                    INNER JOIN cts_JZInfoImg C
                                        ON B.Gid=C.DBInfoId
                                    WHERE A.sys_IsDelete=0
                                            AND B.sys_IsDelete=0
                                            AND C.sys_Creater='{userId}' 
                                            AND C.Type<7 ) A
                                INNER JOIN 
                                    (SELECT DBInfoId,
                                        COUNT(0) AS PersoncCount FROM
                                        (SELECT DISTINCT C.sys_Creater,
                                        C.sys_CreaterName,
                                        C.DBInfoId
                                        FROM cts_JZInfo A
                                        INNER JOIN cts_JZPerson B
                                            ON A.DBPersonId=B.Gid
                                        INNER JOIN cts_JZInfoImg C
                                            ON B.Gid=C.DBInfoId
                                        WHERE A.sys_IsDelete=0
                                                AND B.sys_IsDelete=0                                               
                                                AND C.Type<7 ) t
                                        GROUP BY  DBInfoId ) B
                                        ON A.DBInfoId=B.DBInfoId";

            //日间照料中心 4 积分
            var sql6_all = $@"SELECT ISNULL(SUM(CAST(4.0 / C.PersonCount AS decimal(18,2))),0) AS RijianCount
                                FROM dbo.cts_AssessRijian AS A
                                INNER JOIN 
                                    (SELECT DISTINCT OrgId,
                                         sys_Creater,
                                         sys_CreaterName
                                    FROM dbo.cts_RijianAssessInfo WHERE sys_Creater='{userId}'  ) AS B
                                    ON A.Gid = B.OrgId
                                INNER JOIN 
                                    (SELECT OrgId,
                                         COUNT(0) AS PersonCount
                                    FROM 
                                        (SELECT DISTINCT OrgId,
                                         sys_Creater,
                                         sys_CreaterName
                                        FROM dbo.cts_RijianAssessInfo  ) AS t
                                        GROUP BY  OrgId) AS C
                                        ON A.Gid = C.OrgId
                                WHERE (A.Name <> '' AND A.sys_IsDelete=0)";

            //幸福院 2 积分 _当月
            var sql7_all = $@" SELECT  ISNULL( SUM( CAST(2.0 / C.PersonCount AS decimal(18, 2))),0) AS XFYCount
                                FROM dbo.cts_AssessXFY AS A
                                INNER JOIN 
                                    (SELECT DISTINCT XFYId,
                                         sys_Creater,
                                         sys_CreaterName
                                    FROM dbo.cts_XFYAssessInfo WHERE sys_Creater='{userId}'  ) AS B
                                    ON A.Gid = B.XFYId
                                INNER JOIN 
                                    (SELECT XFYId,
                                         COUNT(0) AS PersonCount
                                    FROM 
                                        (SELECT DISTINCT XFYId,
                                         sys_Creater,
                                         sys_CreaterName
                                        FROM dbo.cts_XFYAssessInfo  ) AS t
                                        GROUP BY  XFYId) AS C
                                        ON A.Gid = C.XFYId
                                WHERE (A.Name <> '' AND A.sys_IsDelete=0) "; 
            var monCount1 = _assessPersonRep.AsSugarClient().SqlQueryable<dynamic>(sql1).First();
            var monCount2 = _assessPersonRep.AsSugarClient().SqlQueryable<dynamic>(sql2).First();
            var monCount3 = _assessPersonRep.AsSugarClient().SqlQueryable<dynamic>(sql3).First();
            var monCount4 = _assessPersonRep.AsSugarClient().SqlQueryable<dynamic>(sql4).First();
            var monCount5 = _assessPersonRep.AsSugarClient().SqlQueryable<dynamic>(sql5).First();
            var monCount6 = _assessPersonRep.AsSugarClient().SqlQueryable<dynamic>(sql6).First();
            var monCount7 = _assessPersonRep.AsSugarClient().SqlQueryable<dynamic>(sql7).First();


            var allCount1 = _assessPersonRep.AsSugarClient().SqlQueryable<dynamic>(sql1).First();
            var allCount2 = _assessPersonRep.AsSugarClient().SqlQueryable<dynamic>(sql2).First();
            var allCount3 = _assessPersonRep.AsSugarClient().SqlQueryable<dynamic>(sql3).First();
            var allCount4 = _assessPersonRep.AsSugarClient().SqlQueryable<dynamic>(sql4).First();
            var allCount5 = _assessPersonRep.AsSugarClient().SqlQueryable<dynamic>(sql5).First();
            var allCount6 = _assessPersonRep.AsSugarClient().SqlQueryable<dynamic>(sql6).First();
            var allCount7 = _assessPersonRep.AsSugarClient().SqlQueryable<dynamic>(sql7).First();
            var MonCount = monCount1 + monCount2 + monCount3 + monCount4 + monCount5 + monCount6 + monCount7;
            var AllCount = allCount1 + allCount2 + allCount3 + allCount4 + allCount5 + allCount6 + allCount7;

            return new { MonCount, AllCount } ;
        }

        #endregion
    }
}
